SQL CONSTRAINT clauses: PRIMARY KEY and UNIQUE

In SQL Server, SQL CONSTRAINT clauses let you control the kinds of values that get entered into a column. Learn about two of them here.

This is the first of two parts on various SQL CONSTRAINT clauses in SQL Server. This one covers the PRIMARY KEY

and UNIQUE constraints, while the second part explains FOREIGN KEY, CHECK and DEFAULT constraints.

SQL Server lets you define table constraints that help ensure data integrity. A SQL constraint enforces rules that control the types of values that you insert into a column beyond the controls provided by a column's data type and nullability. For example, you can define a SQL constraint specifying that each value in a column must be unique or lie within a specific range.

To add a SQL constraint to a table, you include a CONSTRAINT clause as part of your column or table definition. The clause supports five types of constraints: PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK and DEFAULT. Each type serves a specific purpose in ensuring that the values inserted into a column conform precisely to the business rules that govern the data.

The PRIMARY KEY SQL constraint

A PRIMARY KEY constraint is made up of one or more columns whose values uniquely identify each row in a table. You can define only one primary key on a table. Any values you insert into the primary key columns must be unique. If you define a primary key on multiple columns, that uniqueness refers to the columns as a whole; the individual columns can contain duplicate values, but no value sets can be duplicated. For example, suppose you define a primary key on the StoreID and ProductID columns in a table that contains product data. The columns might contain data similar to the following:

StoreID ProductID
101 4297
101 3822
101 6944
102 8822
102 4297
102 3822
103 6944
103 8822

Notice that each column contains duplicate values, but no value pairs are duplicated. As a result, each set of values uniquely identifies each row of data, even if data in other columns is duplicated.

The database engine enforces the uniqueness of the PRIMARY KEY constraint by creating a clustered or nonclustered unique index. If you create the primary key without specifying the index type, the database engine will create a clustered index if no other clustered index exists. (Only one clustered index can be defined on a table.) In addition, all primary key columns must be configured as NOT NULL. If the column definition doesn't explicitly specify NOT NULL, the primary key columns will be set to NOT NULL.

To create a primary key on a table, you must include the CONSTRAINT clause in the column or table definition. For example, the following CREATE TABLE statement defines a primary key as part of the ProductID column definition:

CREATE TABLE SpecialtyProducts

(
  ProductID INT NOT NULL
    CONSTRAINT pk_ProductID PRIMARY KEY CLUSTERED,   ProductName NVARCHAR(50) NOT NULL );

You simply add the CONSTRAINT clause onto the column definition. After specifying the CONSTRAINT keyword, you provide a name for the constraint (in this case, pk_ProductID), followed by the PRIMARY KEY keywords and, optionally, the CLUSTERED keyword.

You can also use the ALTER TABLE statement to add or update a constraint. In this article, we focus on the CREATE TABLE statement, but the CONSTRAINT clause is fairly similar in the ALTER TABLE statement. You can go here for more information.

When defining a PRIMARY KEY constraint as part of the column definition, you can shorten the constraint definition by dropping the CONSTRAINT keyword, constraint name and index type:

CREATE TABLE SpecialtyProducts

(
  ProductID INT PRIMARY KEY,
  ProductName NVARCHAR(50) NOT NULL
);

As the example shows, creating a primary key on a single column requires only that you add the PRIMARY KEY keywords. The database engine automatically configures the nullability and index type as NOT NULL and CLUSTERED, respectively, in addition to generating the constraint name.

Another way you can create the primary key is to add a CONSTRAINT clause as a table constraint, as shown in the following example:

CREATE TABLE SpecialtyProducts

(

  ProductID INT NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  CONSTRAINT pk_ProductID

    PRIMARY KEY CLUSTERED (ProductID)

);

In this case, you include the CONSTRAINT clause as you would a column definition. You must specify the CONSTRAINT keyword, constraint name and PRIMARY KEY keywords. The index type is optional. In addition, you must also specify the primary key column, in parentheses. The advantage to defining a table constraint is that it lets you configure a primary key on multiple columns. For example, the CONSTRAINT clause in the following CREATE TABLE statement defines the primary key on the StoreID and ProductID columns.

CREATE TABLE SpecialtyProducts

(
  ProductID INT NOT NULL,
  ProductName NVARCHAR(50) NOT NULL,
  CONSTRAINT pk_ProductID
    PRIMARY KEY CLUSTERED (ProductID)
);

Now the values in both the StoreID and ProductID columns, when taken together, ensure that each row in the table is unique.

For single-column primary keys, you can choose to add a column or table constraint. The approach you take depends, in part, on your organization's development standards, but whichever approach you do take, the big advantage to using the full CONSTRAINT clause is that you can name your constraints, making them much easier to work with than the auto-generated names.

The UNIQUE SQL constraint

A UNIQUE constraint can be defined on one or more columns as an alternative to a PRIMARY KEY constraint. Like a PRIMARY KEY constraint, a UNIQUE constraint enforces uniqueness; however, it also permits null values. UNIQUE constraints are particularly handy when a primary key is already defined on a table and you want to ensure that the values in one or more columns are unique.

When you create a UNIQUE constraint, the database engine creates a clustered or nonclustered index to enforce the uniqueness. If you don't specify the index type, the engine creates a nonclustered index. You can create a clustered unique index only if no clustered index is already defined on the table.

To add a UNIQUE SQL constraint, you need only include the UNIQUE keyword in your column definition, as shown in the following example:

CREATE TABLE SpecialtyProducts

(
  StoreID INT NOT NULL,
  ProductID INT NOT NULL,
  AltID CHAR(7) NOT NULL UNIQUE,
  ProductName NVARCHAR(50) NOT NULL,
  CONSTRAINT pk_StoreID_ProductID     PRIMARY KEY CLUSTERED (StoreID, ProductID) );

As with PRIMARY KEY constraints, you can include the CONSTRAINT keyword and provide a constraint name, but if you don't, as in the example above, the database engine assumes the CONSTRAINT keyword and automatically generates the constraint name. You can also specify the index type after the UNIQUE keyword, but this is necessary only if you want to create a clustered index.

SQL Server also lets you define a UNIQUE constraint as a table constraint. As in the previous example, the following CREATE TABLE statement defines a UNIQUE constraint on the AltID column:

CREATE TABLE SpecialtyProducts

(
  StoreID INT NOT NULL,
  ProductID INT NOT NULL,
  AltID CHAR(7) NOT NULL,
  ProductName NVARCHAR(50) NOT NULL,
  CONSTRAINT pk_StoreID_ProductID     PRIMARY KEY CLUSTERED (StoreID, ProductID),   CONSTRAINT uq_AltID
    UNIQUE NONCLUSTERED (AltID) );

When creating a table constraint, you must specify the CONSTRAINT keyword, constraint name and UNIQUE keyword. You can optionally include the index type (in this case, NONCLUSTERED). However, you must also provide the column or columns on which the constraint is being defined. If defining the constraint on multiple columns, you must use a table constraint.

FOREIGN KEY, CHECK and DEFAULT constraints >>

About the author:
is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.

This was first published in October 2013

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close