Difference between Primary key and Unique key
Prerequisite – Keys in Relational Model
A primary key is a column of table which uniquely identifies each tuple (row) in that table. Primary key enforces integrity constraints to the table. Only one primary key is allowed to use in a table. The primary key does not accept the any duplicate and NULL values. The primary key value in a table changes very rarely so it is chosen with care where the changes can occur in a seldom manner. A primary key of one table can be referenced by foreign key of another table.
For better understanding of primary key we take table named as Student table, having attributes such as Roll_number, Name, Batch, Phone_number, Citizen_ID.
The roll number attribute can never have identical and NULL value, because every student enrolled in a university can have unique Roll_number, therefore two students cannot have same Roll_number and each row in a table is uniquely identified with student’s roll number. So, we can make Roll_number attribute as a primary key in this case.
Unique key constraints also identifies an individual tuple uniquely in a relation or table. A table can have more than one unique key unlike primary key. Unique key constraints can accept only one NULL value for column. Unique constraints are also referenced by the foreign key of another table. It can be used when someone wants to enforce unique constraints on a column and a group of columns which is not a primary key.
For better understanding of unique key we take Student table with Roll_number, Name, Batch, Phone_number, and Citizen_ID attributes.
Roll number attribute is already assigned with the primary key and Citizen_ID can have unique constraints where each entry in a Citizen_ID column should be unique because each citizen of a country must have his or her Unique identification number like Aadhaar Number. But if student is migrated to another country in that case, he or she would not have any Citizen_ID and the entry could have a NULL value as only one NULL is allowed in the unique constraint.
Key Differences Between Primary key and Unique key:
Primary key will not accept NULL values whereas Unique key can accept NULL values.
- A table can have only one primary key whereas there can be multiple unique key on a table.
- A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.
|Parameter||PRIMARY KEY||UNIQUE KEY|
|Basic||Used to serve as a unique identifier for each row in a table.||Uniquely determines a row which isn’t primary key.|
|NULL value acceptance||Cannot accept NULL values.||Can accepts NULL values.|
|Number of keys that can be defined in the table||Only one primary key||More than one unique key|
|Index||Creates clustered index||Creates non-clustered index|
|Auto Increment||A Primary key supports auto increment value.||A unique key does not supports auto increment value.|