Applying Unique Key Constraint on the column which you want to be unique apart from the Primary key.

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:

1

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.