Export (0) Print
Expand All

Creating and Modifying UNIQUE Constraints

SQL Server 2000

  Topic last updated -- July 2003

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 contains only unique 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™ 2000 by default checks the existing data in the columns to ensure all values 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

Transact-SQL

Enterprise Manager

To create a UNIQUE constraint on an existing table

Transact-SQL

Enterprise Manager

SQL-DMO

To modify a UNIQUE constraint

Enterprise Manager

To delete a UNIQUE constraint

Transact-SQL

Enterprise Manager

SQL-DMO

See Also

UNIQUE Constraints

Show:
© 2014 Microsoft