Difference Between Primary key and Unique key

We have already studied different types of keys used in database and schema in the previous articles namely Difference Between Primary key and Foreign Key. In this article, we are distinguishing Primary key and Unique key. Both primary key and the unique key is used to identify a tuple uniquely and enforces uniqueness in a column or combination of a column.

The essential difference between primary key and unique key is that primary key does not accept NULL values whereas NULL values are allowed within Unique key constraints.

Content: Primary key Vs Unique key

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

Comparison Chart

Basis for comparison	Primary key	Unique key
Basic	It is used to serve as a unique identifier for each row in a table.	It also uniquely determines a row, that is not defined as a primary key.
NULL value acceptance	Primary key will not accept NULL values.	Unique will accept a NULL value.
Number of keys that can be defined in the table	Only one primary key	More than one
Index	Creates clustered index	Creates non-clustered index

Definition of Primary key

A column can be called as a primary key of its table if it uniquely identifies each tuple (row) in that table. It enforces integrity constraints to the table. Only one primary key is allowed in a table. The primary key does not accept the duplicate and NULL values. The primary key is chosen with care where the changes can occur in a seldom manner, means that a primary key in a table changes very rarely.

Let’s understand the notion of primary key with the help of a table. Here we are creating a table named as Student table, which has attributes such as Roll_number, Name, Batch, Phone_number, Citizen_ID.In the given example, the attribute roll number can never have identical and NULL value, because every student enrolled in a university with a unique Roll_number. No two students can have same Roll_number, and each row in a table is uniquely identified with students’ roll number. So, we can make Roll_number attribute as a primary key in this particular case.

A primary key can be referenced by foreign key. It creates a unique clustered index on the table. In a clustered index the data rows are sorted and stored in a table or views on the basis of its key values. There can be only one clustered index in a table, the reason behind this is that a data rows in a table can be sorted in only one order.

Definition of Unique key

Similar to a primary key, Unique key constraints also identifies an individual tuple uniquely in a relation. But, there are certain differences between them. A table can have more than one unique key. Unique key constraints can accept only one NULL value for a column.

Let’s understand this with the similar example, where we had a Student table with Roll_number, Name, Batch, Phone_number and Citizen_ID attributes. Roll number attribute is assigned with the primary key.
Here Citizen_ID can be assigned with unique constraints where each entry in a Citizen_ID column should be unique, not duplicate because each citizen of a country must have his or her Unique identification number. But, if a student migrates from another country, in that case, he or she would not have the Citizen_ID and the entry could have a NULL value as one NULL is allowed in the unique constraint.

Unique constraints are also referenced by the foreign key. It can be used when someone wants to enforce constraints on a column and a group of columns which is not a primary key. Unlike the primary key, it generates the non-clustered index. Non-clustered indexes have a distinct structure from the data rows. Each key-value entry in it points to the data row that contains the key value hence it uses pointers.

Conclusion

Primary key and Unique key both serve the purpose of a unique identifier for the rows of a table with the unique values in a column or group of columns. These key constraints are significantly differentiated where every table can have at most one primary key while a table can have multiple unique keys that are not primary.