Difference between Primary key and Unique key
Prerequisite – Keys in Relational Model
Keys have a crucial role in Relational Algebra in DBMS. There are several types of Keys like Primary Key, Candidate Key, Super Key, Foreign Key, Unique Key, Alternate Key, etc. In this article, basically, we are going to see the Primary Key, Unique Key, and Key Differences between them.
A primary key is a column of a table that uniquely identifies each tuple (row) in that table. The 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 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 the foreign key of another table.
For a better understanding of the primary key, we take a table named Student table, having attributes such as Roll_number, Name, Batch, Phone_number, and Citizen_ID.
The roll number attribute can never have an identical and NULL value, because every student enrolled in a university can have a unique roll number, therefore two students cannot have the same roll number, and each row in a table is uniquely identified with the student’s roll number. So, we can make Roll_number attribute as a primary key in this case.
Features of Primary Key
Some of the essential features of Primary Keys are discussed below.
- There will be no duplicate row in case of a Primary Key.
- Only a single primary key exists for a table.
- Primary Key contains NOT NULL constraints.
- The primary Key can be made from one or more table fields.
Unique Key constraints also identify an individual tuple uniquely in a relation or table. A table can have more than one unique key, unlike a primary key. Unique key constraints can accept only one NULL value for the 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 a better understanding of the unique key, we take the 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 an Aadhaar Number. But if the 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.
Features of Unique Key
Some of the essential features of Unique Keys are discussed below.
- There can be more than one unique key for a table.
- Unique Keys have the liberty of having NULL values in the column.
- Unique Keys can be formed from one or more tables.
- Foreign Keys can refer to Unique Keys for referencing.
Differences between Primary Key and Unique Key
|Parameters||PRIMARY KEY||UNIQUE KEY|
|Basic||Used to serve as a unique identifier for each row in a table.||Uniquely determines a row that isn’t the primary key.|
|NULL value acceptance||Cannot accept NULL values.||Can accept 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 support auto-increment value.|
|Modification||We cannot change or delete values stored in primary keys.||We can change unique key values.|
|Uses||The primary Key is used for indicating the rows uniquely.||The Unique Key is used for preventing duplicate entries.|
CREATE TABLE Student
Student_Id INT PRIMARY KEY,
CREATE TABLE House
House_Number INT UNIQUE,
- The primary key will not accept NULL values whereas the Unique key can accept NULL values.
- A table can have only one primary key whereas there can be multiple unique keys on a table.
- A Clustered index is automatically created when a primary key is defined whereas a Unique key generates the non-clustered index.
- A Primary Key can be a Unique Key, but a Unique Key cannot be a primary key.
Please Login to comment...