Difference Between Primary Key and Unique Key

In relational database system primary key and unique key are two important concepts. Both are used to uniquely identify a row in a table. Though both can identify a row uniquely but there is some difference between them. This article explains a comparison between primary key and unique key. Summary of the article:

• What is a Key?
• What is Unique Key?
• What is Primary Key?
• Primary Key VS Unique Key

What is a Key?
A column or a set of columns, which can be used to identify or access a row or a set of rows in a database is called a key.

What is Unique Key?
A unique key is a column or a set of columns that can uniquely identify a row in a table. So, a unique key is constrained such that no two values of it are equal. Unique key allows only a single NULL value in that column. Depending on the design of a database, a table may have more than one unique key.

What is Primary Key?
A primary key is a column or a set of columns that can uniquely identify a row in a table. A table can have at most one primary key. A primary key have an implicit NOT NULL constraint. So, a column that is defined as the primary key cannot have NULL values in it.

Primary Key VS Unique Key
Even though both the primary key and unique key are one or more columns that can uniquely identify a row in a table, they have some important differences. The difference between primary key and unique key in SQL is given bellow:
Primary Key

1. A table can have only one primary key
2. A primary key have an implicit NOT NULL constraint
3. It cannot contain NULL values

Unique Key

1. A table can have more than one unique key
2. A unique key have not  implicit NOT NULL constraint
3. It may or may not contain NULL values

For proper database design and management, we should have clear concept about primary key and unique key. This is also a common interview question. So don’t be confused on that time. The differences are same for all popular relational database (MS SQL, Oracle, MySQL).