UNIQUE Constraints in Microsoft SQL Server

By creating a UNIQUE constraint, SQL Server administrators specify that a database 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 duplicates, the constraint creation command fails. Similarly, after you define 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 to specify several 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

The easiest way to create a unique constraint in Transact-SQL is to connect to a Database Engine in Object Explorer in SQL Management Studio and then click New Query.

Use the following query, modifying the terms as necessary, to create a new table and add a constraint on a column:

USE AdventureWorks2012; 
GO 
CREATE TABLE Production.TransactionHistoryArchive4 
 ( 
 TransactionID int NOT NULL, 
 CONSTRAINT AK_TransactionID UNIQUE(TransactionID) 
); 
GO

Execute the query.

Similarly, to create a unique constraint on an existing table, execute the following T-SQL query:

USE AdventureWorks2012; 
GO 
ALTER TABLE Person.Password 
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt); 
GO

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 T-SQL commands to create them (ALTER TABLE and 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. Note several differences, though:

• 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.