What is Unique key and Primary key in SQL?

What is Unique key and Primary key in SQL? This is one of the most frequently asked interview question.

Before we discuss difference between these two let us see what is primary key and unique key.

What is Primary key?

Primary key is the key which uniquely identify each and every record in the table.

If we have a primary key on a table you can identity each and every row in the table uniquely using primary key.

What is Unique key?

Unique key will make sure only unique values are allowed on the column in which it is defined.

Similarities: Primary key vs Unique key

If we see the similarities between unique key and primary key .

Both allow only unique values it means avoid duplicate values.

Which are used to uniquely identity each row on a table.

Difference between Unique and Primary key

Here you can see the major differences between unique and primary key.

unique key vs primary key

Now let us create a table to understand both primary key and unique key

create primary key and unique key

Table can have only one Primary key

If you can observe the below table and script. When we execute with two primary keys it says cannot add two primary keys. So a table can have only one primary key.

primary key violation

Table can have more than one Unique key

If you can just observe the below table. We have successfully executed the script with two unique keys.

It means a table can have more than one unique key.

more than one unique key

Sample Data Insertion

Here we have inserted the data for both unique and primary key.

primary key and unique key data insertion

Primary key does not allow duplicate values

Here we just tried to insert the same data by changing one unique key value.

You can see the primary key violation that a table can have only one primary key.

key violation

Unique key does not allow duplicate values

Here we tried to insert the duplicate unique key value and we got violation of unique key constraint. Means unique key does not allows duplicate values.

unique key constraint

Primary does not allow NULL values

Here we tried to insert a null value into the primary key and got an error cannot insert the null value. Means null values are not allowed on primary key.

primary key no null

One null value allowed on unique key

Row inserted

Duplicates are not allowed on Unique key.

Unique key allows one null value. When we try to insert one more null value it shows violation on unique. key. It means unique key allows only one null value.

unique key no duplicate values

Let us see the sp help for customer table here.

sp help

If you can observe here by default primary key creates unique clustered index and non unique key creates unique non clustered index.

Index on keys

I hope this article is useful to you. In case of any queries you can leave your comment below.

You can read our related article SQL server management studio.

Leave a comment