SQL Server: Unique Key Constraints

The Unique Constraint ensures the uniqueness of all values and no duplicate values are entered in a column of a table.

The unique constraints can be created using CREATE or ALTER TABLE T-SQL. Add unique constraints after columns definitions in the CREATE TABLE statement.

CONSTRAINT <constraint_name> UNIQE(<column_name>)  

The following CREATE TABLE statement defines a unique key constraint on the PhoneNo column of the Employee table.

CREATE TABLE Employee(EmployeeID int,    FirstName nvarchar(50) NOT NULL,      LastName nvarchar(50) NOT NULL,     EMail nvarchar(50),    PhoneNo varchar(15),    ADD CONSTRAINT UNQ_Emp_Phone Unique(PhoneNo))

The following ALTER TABLE statement adds a unique constraint on the PhoneNo column of the existing Employee table.

ALTER TABLE Employee   ADD CONSTRAINT UNQ_Emp_Phone Unique(PhoneNo)

Now, if you try to insert or update a record in the Employee table where Phone is not within the specified range, then it will raise the following error.

Important Points:

• Both Unique constraint and Primary key constraint enforce uniqueness. It is recommended to use Unique constraint instead of Primary key constraint whenever you want to enforce uniqueness in a column.
• Unlike Primary key constraint, Unique constraints allow only one NULL value.
• A unique index is automatically created when a unique key constraint is created.
• SQL Server raises an error whenever a duplicate value is inserted or updated.
• When a Unique index is added to an existing column in a table, the database engine first checks for the uniqueness of the specified column data. If a duplicate entry is found, then the engine returns an error and does not add the constraint.
• A Unique key in a table can be referenced by a Foreign Key from another table.

Create Unique Constraint Using SSMS

Step 1: Open SSMS, login to a database. In the Object Explorer, expand the table folder and right-click on a table where you want to add a unique constraint and select Design to open it in a table designer.

Now, right-click on the table designer and select Indexes/Keys,as shown below.

Step 2: In the "Indexes/Keys" dialog box, select PhoneNo column in the Columns property.

Next, select Unique Key type to apply the unique key constraint on the PhoneNo column.

Step 3: give the name of a constraint in the name property.

Click Close and Save the table.

Thus, you can create an unique key constraint on a column in a table.

Delete Unique Key Constraint

Use the DROP CONSTRAINT statement in the ALTER TABLE statement to delete existing unique constraints.

ALTER TABLE DROP CONSTRAINT <constraint_name>;

The following deletes a unique key constraint UNQ_Emp_Phone in the Employee table.

ALTER TABLE Employee   DROP CONSTRAINT UNQ_Emp_Phone;

To delete constraints using SQL Server Management Studio, expand the table having the constraint to be deleted.

Now, expand Keys folder and right-click on a constraint to be deleted and click Delete.

This will open "Delete Object" dialog box, as shown below.

In the "Delete Object" dialog box, click Ok to delete a constraint.