Creating and Modifying UNIQUE Constraints

UNIQUE constraints can be:

• Created when the table is created, as part of the table definition.
• Added to an existing table, provided that the column or combination of columns comprising the UNIQUE constraint contain only unique or NULL values. A table can contain multiple UNIQUE constraints.
• Modified or deleted if they already exist. 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.

  ---

  Note To modify a UNIQUE constraint using Transact-SQL or SQL-DMO, you must first delete the existing UNIQUE constraint and then re-create it with the new definition.

  ---

When a UNIQUE constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ by default checks the existing data in the columns to ensure all values, except NULL, are unique. If a UNIQUE constraint is added to a column that has duplicated values, SQL Server returns an error and does not add the constraint.

SQL Server 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, SQL Server returns an error message that says 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.

Delete a UNIQUE constraint to remove the uniqueness requirement for values entered in the column or combination of columns included in the constraint. It is not possible to delete a UNIQUE constraint if the associated column is used as the full-text key of the table.

To create a UNIQUE constraint when creating a table

To create a UNIQUE constraint on an existing table

To modify a UNIQUE constraint

To delete a UNIQUE constraint

See Also

UNIQUE Constraints