Creating and Modifying UNIQUE Constraints

You can create a UNIQUE constraint as part of the table definition when you create a table. If a table already exists, you can add a UNIQUE constraint, provided that the column or combination of columns that make up the UNIQUE constraint contains only unique values. A table can contain multiple UNIQUE constraints.

If a UNIQUE constraint already exists, you can modify or delete it. For example, you may want the UNIQUE constraint of the table to reference other columns, or you may want to change the type of index clustering.

When a UNIQUE constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure all values are unique. If a UNIQUE constraint is added to a column that has duplicated values, the Database Engine returns an error and does not add the constraint.

The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

The following excerpt of a CREATE TABLE statement shows the definition of the Name column, specifying a UNIQUE constraint on this column to make sure the values are unique.

Name nvarchar(100) NOT NULL

UNIQUE NONCLUSTERED

To remove the uniqueness requirement for values entered in the column or combination of columns included in the constraint, delete a UNIQUE constraint. You cannot delete a UNIQUE constraint if the associated column is used as the full-text key of the table.

To create a UNIQUE constraint when you create a table

• CREATE TABLE (Transact-SQL)

To create a UNIQUE constraint on an existing table

• ALTER TABLE (Transact-SQL)

To delete a UNIQUE constraint

• ALTER TABLE (Transact-SQL)

To obtain information about UNIQUE constraints

• sys.key_constraints (Transact-SQL)