UNIQUE Constraints in Microsoft SQL Server

The Advantages of Using UNIQUE Constraints Over Primary Key Constraints

SQL code on black background

By creating a UNIQUE constraint, SQL Server administrators specify that a column may not contain duplicate values. When you create a new UNIQUE constraint, SQL Server checks the column in question to determine whether it contains any duplicate values. If the table contains pre-existing duplicates, the constraint creation command fails. Similarly, once you have a UNIQUE constraint on a column, attempts to add or modify data that would cause duplicates to exist also fail. 

Why Use UNIQUE Constraints

A UNIQUE constraint and a primary key both enforce uniqueness, but there are times that a UNIQUE constraint is the better choice.

  • Use a UNIQUE constraint when you want multiple constraints to a table. You can only attach one primary key to a table.
  • Use a UNIQUE constraint when a column permits null values. Primary key constraints can only be attached to columns that don't permit null values.

Creating a UNIQUE Constraint

There are many ways you can create a UNIQUE constraint in SQL Server. If you want to use Transact-SQL to add a UNIQUE constraint on an existing table, you can use the ALTER TABLE statement, as illustrated below:

If you prefer to interact with SQL Server using GUI tools, you can also create a UNIQUE constraint using SQL Server Management Studio. Here’s how:

  1. Open SQL Server Management Studio.

  2. Expand the Tables folder of the database where you want to create the constraint.

  3. Right-click the table where you want to add the constraint and click Design.

  4. In the Table Designer menu, click Indexes/Keys.

  5. In the Indexes/Keys dialog box, click Add.

  6. Choose Unique Key in the Type drop-down list.

UNIQUE Constraints vs. UNIQUE Indexes

There has been some confusion about the difference between a UNIQUE constraint and a UNIQUE index. While you may use different Transact-SQL commands to create them (ALTER TABLE…ADD CONSTRAINT for constraints and CREATE UNIQUE INDEX for indexes), they have the same effect, for the most part. In fact, when you create a UNIQUE constraint, it actually creates a UNIQUE index on the table. It is significant to note, however, that there are several differences:

  • When you create an index, you can add additional options to the creation command.
  • A column subject to a UNIQUE constraint may be used as a foreign key.