Difference between Primary Key and Unique Key

Print 3 min read

31 Aug 2022

Intermediate

12.8K Views

In SQL Server, we have two keys that distinctively or uniquely identify a record in the database. Both the keys seem identical, but actually, both are different in features and in behaviors. In this article, I would like to share the key differences between the primary key and the unique key.

For more help about keys in SQL Server refer to the article Different Types of SQL Keys.

What is the primary key?

The primary key is the values of the certain column that uniquely identifies any row of a table. The primary key cannot have NULL and duplicate values. The primary key is used to add integrity to the specific table as we have a table with unique rows.

While using the primary key with the table, both duplicate and NULL values are not valid for the columns. And, it can be utilized as foreign keys for other different tables accordingly.

What is a unique key?

A unique key is a unique value amongst other values that are used to protect duplication of the values in a column of the table. The primary use of a unique key in a table is to prevent duplicate values. But, when it comes to the unique values, the primary key also includes them. So, there is one big difference that makes a unique key different, and it is that the unique key may have a NULL as a value but the primary key does not allow NULL as a value.

Difference between Primary Key and Unique Key

Primary Key

Unique Key

Primary Key can't accept null values.

Unique key can accept only one null value.

By default, the Primary key is clustered index, and data in the database table is physically organized in the sequence of the clustered index.

By default, the Unique key is a unique non-clustered index.

We can have only one Primary key in a table.

We can have more than one unique key in a table.

The primary key can be made into a foreign key in another table.

In SQL Server, a Unique key can be made foreign key in another table.

Defining Primary key and Unique key in SQL Server

 
CREATE TABLE Employee 
 (
 EmpID int PRIMARY KEY, --define primary key
 Name varchar (50) NOT NULL,
 MobileNo int UNIQUE, --define unique key
 Salary int NULL
 )

Summary

Even though both primary and unique key has a few set of similarities, certainly, primary and unique keys have significant differences such as we can only have one primary key per table, but we are allowed to have multiple unique keys. Same way, a primary key column does not accept NULL values, whereas the unique key columns may contain one null value with each column. And most importantly, the primary key column has a unique clustered index whereas a unique key column has a unique non-clustered index.

I hope you will enjoy these tricks while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.