Difference Between Primary Key And Unique Key Explained!
Table of content:
- What is the need for a key in the database?
- What is a Primary Key?
- What is a Unique Key?
- Primary Key Vs Unique Key
“No Null value + No Duplicate Value= Primary Key
No Duplicate =Unique Key”
Seems Confusing! Yes? So let’s start and understand Primary Key and Unique key in a simpler way.
What is the need for a key in the database?
We know that in Relational Database, we have rows and columns. Columns do have particular names like Aadhar Number, Roll no, Marks, etc. So if we want to access the data of a particular column we can easily do so by selecting the column with the given name. But what if we want to access a particular row of a table? Rows don’t have any specific name then how can we access a particular row. So there must be some property, attribute, or method through which we can uniquely identify a particular row from the database. Here is when keys come into the picture. So we can say that keys are the essential elements of any relational database. It identifies each tuple in a relation uniquely and is used to get records from tables. Keys can be made from a single column or a combination of columns.
There are many keys that we use in SQL and database like Primary key, Unique key, Foreign key, Super Key, Alternate Key, Composite Key, etc. But here in this article, we will discuss only the Primary Key And Unique Key.
What is a Primary Key?
- A primary key is a key in a relational database that is selected by Database Administrator as a primary means to uniquely identify a tuple or row in a database table.
- A primary key can also be defined as that candidate key of the relational table which is selected by the Database Administrator as a primary means to uniquely identify a tuple or row in a database table.
It is a unique identifier, such as a driver’s license number, telephone number (including area code), or PAN number. It is used to enforce entity integrity to the table. It doesn’t allow any duplicate value and null value in the database table. Only a single primary key per table is allowed. It is useful when we do not want to keep NULL value in the table.
Now, let’s consider an Employee Table with the following fields:
In Table Employee, Emp_Id can be a perfect primary key for the table because Emp_Id is always unique and can never be null. Therefore in the above table, we can consider Emp_id as the primary key column for our employee database record.
We may also argue that why can’t we take Emp_Email id as the primary key? This is because Emp_Email id is unique for each employee but it may be possible some employees don’t have an email id and that column is null in that case. Since the primary key doesn’t allow nulls so it can’t be a primary key for the database table. Column Emp_Department and Emp_Name may have identical values for the same employee so they also can’t be considered as Primary Key.
Features Of Primary Key
- It doesn’t allow duрliсаte vаlues.
- It can be made from one or more columns of the table.
- It doesn’t allow NULL value.
- Only a single primary key per table is allowed.
- It imрlements the entity integrity оf the tаble.
What is a Unique Key?
- The unique Key is very similar to the primary key except for the fact that the primary key doesn’t allow null values in the column but the unique key allows null in the column. So Unique key can be defined as a unique identifier for rows in a database table that doesn’t allow duplicate value and can uniquely identify a row/tuple in the database table. We can make Unique Key from one or more table fields.
- It is used to enforce unique constraints on a column and a group of columns which is not a primary key. Now let’s again consider the Employee Table :
In this we want Emp_Email to be unique and it may also contain Null values so we may enforce unique integrity constraints on the Emp_Email column and hence it becomes Unique Key.
Features Of Unique Key
- Unique Keys can be made from one or more columns.
- Multiple Unique keys per table are allowed.
- It is in non-clustered unique indexes by default.
- It allows NULL value, but only one NULL is allowed per column
Primary Key Vs Unique Key
Sr.NoPrimary KeyUnique Key1
A primary key is a key in a relational database that is selected by Database Administrator as a primary means to uniquely identify a tuple or row in a database table.
A unique key can be defined as a unique identifier for rows in a database table that doesn’t allow duplicate values and can uniquely identify a row/tuple in the database table.2It is clustered unique index by default which means data is organized in the clustered index sequence.It is a non-clustered unique index by default.3It doesn’t allow null values.It allows only 1 null value.4There can be only 1 primary key in a table.There can be multiple Unique keys in a table.5
It implements the entity integrity of the table.
It enforces unique constraints.6
We cannot change or delete the primary key values.
We can modify the unique key column values.7
Here is how we define a single column EMP_ID as the primary key.
create Table Employee(
Emp_Id int NOT NULL PRIMARY KEY
Here is how we define a single column EMP_ID as the unique key.
create Table Employee(
Emp_Id int NOT NULL UNIQUE
We have concluded that the key difference between the primary key and the unique key is that the primary key doesn’t allow null value while the unique key constraint allows nulls value. The primary key constraint is clustered unique index by default while the unique key is non-clustered index by default. So unique key constraint is useful when we don’t want columns to have duplicate values and a primary key constraint is useful when we want columns neither to have duplicate values nor null values. A primary key must be unique but a unique key does not necessarily have to be the primary key.
You might also be interested in reading: