How to create a unique constraint in SQL Server Table Designer

Developers 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, developers should attach a unique constraint instead of a primary key constraint to a table if:

– They want to enforce uniqueness in a column or combination of columns. They can attach multiple unique constraints to a table, whereas they can attach only one primary key constraint to a table.

-They want to enforce uniqueness in a column that allows null values. They can attach unique constraints to columns that allow null values, whereas they can attach primary key constraints only to columns that do not allow null values. When they attach a unique constraint to a column allowing null values, they ensure that at most one row will have a null value in the constrained column.

 

Developers can create a unique constraint, by following the next steps:

1. In Object Explorer, right-click the table to which they want to add a unique constraint, and click Design (Modify in SP1 or earlier).

The table opens in Table Designer.

2. From the Table Designer menu, click Indexes/Keys.

3. In the Indexes/Keys dialog box, click Add.

4. In the grid, click Type and choose Unique Key from the drop-down list box to the right of the property.

The unique constraint is created in the database when developers save the table.