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.

Syntax:
CONSTRAINT <constraint_name> UNIQE(<column_name>)  

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

Example: Add Unique Constraint in Existing 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.

Example: Add Unique Constraint in Existing 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.

Violation of UNIQUE KEY constraint 'UNQ_Emp_Phone'. Cannot insert duplicate key in object 'dbo.Employee'. The duplicate key value is (123.123.1834).The statement has been terminated.

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.

SQL Server Unique Key Constraint

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.

Add Unique Key Constraint

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

Add Unique Constraint Expression

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.

Syntax:
ALTER TABLE DROP CONSTRAINT <constraint_name>;

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

Example: Delete Unique Constraint
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.

Delete Unique Key Constraints

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

Delete Unique Key Constraint

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