Tip

SQL CONSTRAINT clauses: PRIMARY KEY and UNIQUE

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.

    Requires Free Membership to View

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

);

The advantage to defining a table constraint is that it lets you configure a primary key on multiple columns.

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

(

  StoreID INT NOT NULL,

  ProductID INT NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  CONSTRAINT pk_StoreID_ProductID

    PRIMARY KEY CLUSTERED (StoreID, 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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.