Difference between Primary key and Unique key
Last Updated : 25 Sep, 2024
Keys play a crucial role in relational databases by ensuring data integrity and efficient access to records. Keys help to identify individual rows in a table prevent from data being duplicated and also enable reliable relationships between tables. There are several types of Keys - Primary Key, Candidate Key, Super Key, Foreign Key, Unique Key, Alternate Key, etc. In this article, we are going to learn about the Primary Key, and the Unique Key, and the 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 be used 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 rarely. 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 KeyThe 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.
CREATE TABLE Student
(
Roll_number INT NOT NULL, -- Roll_number as the primary key (cannot be NULL)
Name VARCHAR(150),
Batch VARCHAR(50),
Phone_number VARCHAR(15),
Citizen_ID VARCHAR(20), -- Citizen ID can be NULL
PRIMARY KEY (Roll_number) -- Defining Roll_number as the Primary Key
);
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 KeyRoll 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.
CREATE TABLE Student
(
Roll_number INT NOT NULL, -- Roll_number as the primary key (cannot be NULL)
Name VARCHAR(150),
Batch VARCHAR(50),
Phone_number VARCHAR(15),
Citizen_ID VARCHAR(20) UNIQUE, -- Citizen_ID as a unique key, allows one NULL
PRIMARY KEY (Roll_number) -- Defining Roll_number as the Primary Key
);
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.