What is the difference between Primary key and Unique key?

In the last blog, we have learned about Primary Keys and we saw how to find the primary key from a given relation. Apart from the primary key, there is one key called a unique key. Hold on, what is this unique key? Is it the same as that of primary key or there is some difference between these two? In this blog, we will learn about the unique key and we will also see the difference between primary key and unique key. So let's get started by revising the primary key.

Primary Key

The primary key is the minimal set of attributes which uniquely identifies any row of a table. The primary key cannot have a NULL value. It cannot have duplicate values. Primary key enforces the integrity of an entity type. Since a row represents a single instance of a table or entity, the primary key helps in enforcing the integrity of an entity type.

Example: We have a STUDENT table where Roll_no is the Primary key of the table. This Roll_no can be used to uniquely identify the rows of a table.

Note: A table can have only one Primary Key.

Unique Key

Unique Key is a key which has a unique value and is used to prevent duplicate values in a column. But, the primary key also has a unique value for each column. So, how is it different from a primary key? A unique key can have a NULL value which is not allowed in a primary key. But, if a unique key can have NULL values then how can it implement uniqueness in a column? By uniqueness, we mean that if we have a value present then either it would be unique or no value would be present at all. So, the main motive of a unique key in a table is to prevent duplicate values. To clearly understand this let us take an example.

Example: We have a STUDENT table.

In the above table, we have Phone_no as the Unique Key and Roll_no as the Primary Key. So, we can understand if any student has a Phone_no then it should be unique. Also, there might be some student who doesn't have any Phone_no.

Note: A table can have multiple Unique Key.

Difference between Primary Key and Unique Key

• Purpose: Primary Key is used to uniquely identify a row but a unique key is used to prevent duplicate values in a column.
• Existence: A table can have only one primary key but it can have multiple unique keys.

Example: In the Student Table, we have only one Primary Key i.e Roll_no but we have two Unique Key i.e Phone_no and Registration_no.

• Nullability: Primary key cannot have a NULL value but a Unique Key can have multiple NULL values.

Example: In the Student Table, Unique key Phone_no can have a NULL value whereas the Primary Key cannot have any NULL value.

• Modifiability: We cannot change the value of a primary key in a table but we can change the value of the unique key.
• Indexing: By default in the SQL servers, Primary Key creates a clustered index whereas the Unique Key creates the non-clustered index. In the clustered index, the data of the rows are sorted on the basis of their key values. In the non-clustered index, the data is stored at one location and the indices are stored at some other location.

Example: In the given Student table, the primary keys are sorted according to the key values and hence are they create a clustered index. The unique key like Phone_no and the Registration_no have the data stored in the physical files and the pointers are used to link the indices with the files.

Note: Unique Key + Not Null!= Primary Key. This is beacuse the values in the primary key are stored in the clustered index but the values in the unique key are stored in the non-clustered index.

This is how a Primary key differs from a Unique key. Hope you learned something new today.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here.

Keep Learning 🙂

Team AfterAcademy!