Creating a Unique Constraint

Create a unique constraint to ensure no duplicate values are entered in specific columns that do not participate in a primary key. While both a unique constraint and a primary key enforce uniqueness, you should attach a unique constraint instead of a primary key constraint to a table if:

• You want to enforce uniqueness in a column or combination of columns. You can attach multiple unique constraints to a table, whereas you can attach only one primary key constraint to a table.
  
  
• You want to enforce uniqueness in a column that allows null values. You can attach unique constraints to columns that allow null values, whereas you can attach primary key constraints only to columns that do not allow null values. When you attach a unique constraint to a column allowing null values, you ensure that at most one row will have a null value in the constrained column.

To create a unique constraint

1. In your database diagram, right-click the table that will contain the constraint, then select Properties from the shortcut menu.

   -or-

   Open the Table Designer for the table that will contain the constraint, right-click in the Table Designer, and choose Properties from the shortcut menu.

2. Choose the Indexes/Keys tab.
   
   
3. Choose New. A system-assigned name appears in the Index name box.
   
   
4. Under Column name, expand the list of columns and select the column that you want to attach the constraint to. To attach the constraint to multiple columns, select the additional columns in subsequent rows.
   
   
5. Select the Create UNIQUE check box.
   
   
6. Select the Constraint option.

The unique constraint is created in the database when you save the table or the diagram.

If you are using SQL Server, you can control the sort order of key values and the action taken when duplicate keys exist.  To do this, you should create a unique index instead of a unique constraint. For more information, see Creating a Unique Index.

See Also

Constraints | Creating a Unique Index | Deleting a Unique Constraint | Unique Constraints