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