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.
Now let us create a table to understand both 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.
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.
Sample Data Insertion
Here we have inserted the data for both unique and primary key.
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.
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.
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.
One null value allowed on unique key
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.
Let us see the sp help for customer table here.
If you can observe here by default primary key creates unique clustered index and non unique key creates unique non clustered index.
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.