Difference between Primary Key and Unique key

Keys in MySQL are the column or set of columns used to construct a relationship between one or more than two tables. They are also used for accessing records from the table. Both keys provide a guaranteed uniqueness for a column or a set of columns in a table or relation. The main difference among them is that the primary key identifies each record in the table, and the unique key prevents duplicate entries in a column except for a NULL value. In this article, we are going to compare essential differences between Primary and Unique Keys based on various parameters. Before making a comparison, we will discuss in brief these keys.

What is Primary Key?

The primary key is a unique or non-null key that uniquely identifies every record in that table or relation. The primary key column cannot store duplicate values that mean primary key column values are always unique. It is also called a minimal super key; therefore, we cannot specify more than one primary key in any relationship. A primary key column of one table can be referenced by a foreign key column of another table.

For example, we have a table named students with attributes such as Stud_ID, Roll_No, Name, Mobile, and Email.

Here only the Roll_No column can never contain an identical and NULL value. We know every student has a unique roll number. Therefore two students can never have the same roll number. This feature helps to identify each record in the database uniquely. Hence, we can make the Roll_No attribute a primary key.

Features of Primary Key

The following are the essential primary key features:

• The primary key column cannot contain duplicate values.
• The primary key implements the entity integrity of the table.
• A table cannot have more than one primary key column.
• We can make the primary key from one or more table fields.
• The primary key column should have NOT NULL constraints.

What is a Unique Key?

The unique key is a single column or combination of columns in a table to uniquely identify database records. A unique key prevents from storing duplicate values in the column. A table can contain multiple unique key columns, unlike a primary key column. This key is similar to the primary key, except that one NULL value can be stored in the unique key column. The unique key is also called unique constraints and can be referenced by another table's foreign key.

For example, let's consider the same table named students with attributes such as Stud_ID, Roll_No, Name, Mobile, and Email.

Here Stud_ID can be assigned as a unique constraint because each student must have a unique identification number. If a student changes the college, then he or she would not have any student ID. In that case, the entry may contain a NULL value because a unique key constraint allows storing NULL, but it should be only one.

Features of Unique key

The following are the essential unique key features:

• We can construct the unique key from one or more table fields.
• A table can define multiple unique key columns.
• By default, a unique key is in non-clustered unique indexes.
• The unique constraint column can store NULL value, but only one NULL is allowed null per column.
• The foreign key can reference the unique constraint in preserving the uniqueness of a table.

Key differences between Primary and Unique Key

The following points explain the key differences between primary and candidate keys:

• A primary key can constitute one or more fields of a table to identify records in a table uniquely. On the other hand, a unique key prevents two rows from having duplicate entries in a column.
• A table cannot have more than one primary key in a relational database, while there can be multiple unique keys per table.
• A primary key column cannot contain NULL values, whereas a unique key can have NULL values, but only one NULL is allowed in a table.
• A primary key should be unique, but a unique key cannot necessarily be the primary key.
• The primary key by default is a clustered index where data is physically organized in the sequential index. In contrast, the unique key is a unique non-clustered index.
• The primary key implements entity integrity, whereas the unique key enforces unique data.

Primary Key vs. Unique Key Comparison Chart

The following comparison chart explains their main differences in a quick manner:

CREATE TABLE Employee
(
Id INT PRIMARY KEY, 
name VARCHAR(150), 
address VARCHAR(250)
)

CREATE TABLE Person
(
Id INT UNIQUE, 
name VARCHAR(150), 
address VARCHAR(250)
)

Conclusion

In this article, we have made a comparison between primary key and unique key constraints. Here we have concluded that a unique key is useful when we want the columns don't contain duplicate values. And the primary key is useful when we do not want to keep NULL value in the table. It can also be ideal when we have a foreign key in another table to create a relationship among tables.