UNIQUE constraints allow SQL Server administrators to 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 preexisting 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.
Creating a UNIQUE Constraint
There are many ways you can create a UNIQUE constraint in SQL Server. If you wish to use Transact-SQL to add a UNIQUE constraint on an existing table, you may use the ALTER TABLE statement, as illustrated below:
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
If you prefer to interact with SQL Server using GUI tools, you may also create a UNIQUE constraint using SQL Server Management Studio. Here’s how:
- Open SQL Server Management Studio.
- Expand the Tables folder of the database where you wish to create the constraint.
- Right-click the table where you wish to add the constraint and click Design.
- In Table Designer, click Indexes/Keys.
- Click Add.
- Choose Unique Key in the Type drop-down list.
UNIQUE Constraints vs. UNIQUE Indexes
Many database administrators ask 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 may add additional options to the creation command
- A column subject to a UNIQUE constraint may be used as a foreign key