Open In App

Difference between Primary key and Unique key

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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. 

Primary Key

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. 

Primary Key

Primary Key

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

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. 

Unique Key

Unique Key

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.
Syntax

CREATE TABLE Student

(

Student_Id INT PRIMARY KEY, 

Student_name VARCHAR(150), 

roll_number INT(10)

)

CREATE TABLE House

(

House_Number INT UNIQUE, 

House_Name VARCHAR(150), 

House_Address VARCHAR(250)

)

Conclusion

  • 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.

Last Updated : 17 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads