UNIQUE Constraints in Microsoft SQL Server

The Advantages of Using UNIQUE Constraints Over Primary Key Constraints

Format
mla apa chicago
Your Citation
Chapple, Mike. "UNIQUE Constraints in Microsoft SQL Server." ThoughtCo, Aug. 21, 2017, thoughtco.com/unique-constraints-in-microsoft-sql-server-1019841. Chapple, Mike. (2017, August 21). UNIQUE Constraints in Microsoft SQL Server. Retrieved from https://www.thoughtco.com/unique-constraints-in-microsoft-sql-server-1019841 Chapple, Mike. "UNIQUE Constraints in Microsoft SQL Server." ThoughtCo. https://www.thoughtco.com/unique-constraints-in-microsoft-sql-server-1019841 (accessed October 23, 2017).
SQL code on black
KIVILCIM PINAR / Getty Images

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:

 ALTER TABLE 
 ADD CONSTRAINT 
 UNIQUE()

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.
  1. Right-click the table where you want to add the constraint and click Design.
  2. In the Table Designer menu, click Indexes/Keys.
  3. In the Indexes/Keys dialog box, click Add.
  4. 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.