Unique key

constraint enforces the columns to be unique i.e. it won’t allow duplicate values to be inserted in the table column.

So we have a table called Student as shown below:

Here ID is a primary key column i.e. each Student will have its unique Id. But I will able to insert the same EmailId as many times. So in order to restrict the Duplicate EmailId, we are going to create a Unique key constraint on Email column.

So the major difference between a primary key column and Unique key:

• We can have only one primary key but can have more than 1 unique key.
• The primary key cannot be null whereas unique key allows one null.

Creating a unique key using designer mode:

USING SQL Query way:

ALTER TABLE STUDENT 
ADD CONSTRAINT  UK_STUDENT_EMAIL UNIQUE (EMAIL)

Now if I try to insert the same email id again SQL server will give unique constraint error as shown below:

Dropping Unique Key constraint:

ALTER TABLE [dbo].[Student]
DROP CONSTRAINT [UK_Student_EMAIL]
GO 

Happy Learning.