Difference between Primary Key and Unique key



Both Primary key and Unique key both are used to identify records uniquely in a table. These keys are also used to create relationship between tables. A table can have only one primary key whereas there can be multiple unique keys in a single table or relation.

Read this tutorial to learn more about primary keys and unique keys and how they are different from each other.

What is Primary Key?

A primary key is a column or set of columns that uniquely identifies each row in a table. It must be unique across all rows in the table. A primary key is used to enforce the integrity of the data in a table and to ensure that no two rows are identical.

The primary key is basically a constraint that helps in uniquely identifying a record in a relational database. The unique property of the primary key is that there is only one primary key allowed in a table.

The primary key is a combination of the 'UNIQUE' and 'Not Null' constraints. Thus, the column in a relational database which is selected as the primary key can't be a NULL value. Because of this, it is one of the most important parts of a table in a relational database.

A primary key is fundamentally a candidate key. The value of a primary key cannot be deleted from the parent table.

What is Unique Key?

In databases, a unique key (just like a primary key) is a column or set of columns that uniquely identifies each row in a table. However, there can be multiple unique keys present in a table.

Another important characteristic of the unique key is that it allows NULL values. This means that it is possible to have multiple rows with a null value in the unique key column, as long as all other rows have a unique value in that column. The unique keys can also be used as foreign keys for another table.

Unique keys are used to enforce the uniqueness of the data in a column or set of columns, but they are not as strictly enforced as primary keys.

Difference between Primary Key and Unique Key

The following table highlights all the important differences between Primary Key and Unique Key −

Key

Primary Key

Unique Key

Definition

Primary key is used to identify all the records in a relation uniquely.

Unique key is also used to identify a record uniquely.

NULL

Primary key cannot have null value.

Unique key can accept one null value.

Count Wise

There can be only one Primary key per table.

There can be multiple Unique keys in a table.

Index

Primary key creates clustered index.

Unique key creates non-clustered index.

Conclusion

The most significant difference that you should note here is that a primary key and a unique key is that a primary key cannot contain null values and a unique key can.


Advertisements