Create a unique constraint (ADP)

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.

1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
2. In your database diagram, right-click the table that will contain the constraint, then select Indexes/Keys from the shortcut menu.
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 database diagram.

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.