What are the Unique key and Primary keys in SQL?
In this article, we will see What is the Unique key and Primary key in SQL? This comes under one of the most frequently asked interview questions.
Before we discuss the difference between these two let us see what is the primary key and unique key.
What is Primary key?
The primary key is the key that uniquely identifies each and every record in the table.
If we have a primary key on a table you can identify each and every row in the table uniquely using the primary key.
What is Unique key?
The 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 the unique key and the primary key.
Both allow only unique values it means avoid duplicate values.
Which are used to uniquely identify each row on a table.
Difference between Unique and Primary key
Here you can see the major differences between unique and primary keys.
Now let us create a table to understand both the primary key and unique key.
Table can have only one Primary key
If you can observe the below table and the 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 keys.
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 a violation of a unique key constraint. This means a unique key does not allow duplicate values.
Primary Key does not allow NULL values
Here we tried to insert a null value into the primary key and got an error that cannot insert the null value. Means null values are not allowed on the primary key.
One null value allowed on unique key
Duplicates are not allowed on Unique key.
The unique key allows one null value. When we try to insert one more null value it shows a violation on unique. key. It means a 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 a unique clustered index and the nonunique key creates a unique non clustered index.