What is difference between Primary Key and Unique Key

In this post, we want to discuss the difference between the Primary Key and Unique key in SQL Server. Before we get started, if you want to know about the difference between Primary Key and Foreign Key, please go through the article: Primary Key vs Foreign Key.

Introduction

The Primary Key and Unique key are two important concepts in a relational database and are used to uniquely identify a row in a table. In fact, primary key vs unique key is a popular SQL interview question along with classics like truncate vs delete.

Primary Key

A Primary Key is used to ensure data in the specific column is unique. It is a column that cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Unique Key

A Unique Key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. The UNIQUE constraint ensures that all values in a column are different. You can say that it is a little like a primary key but it can accept only one null value and it cannot have duplicate values.

Difference Between Primary Key and Unique Key

1. Function

A Primary Key is sort of a unique key identifier that uniquely identifies a row within a database table, while a Unique Key identifies all possible rows that exist in a table and not just the currently existing rows.

2. Behavior

A Primary Key is used to identify a record in a database table, whereas a Unique Key is used to prevent duplicate values in a column with the exception of a null entry.

3. Indexing

A Primary Key creates a clustered unique index by default while a Unique Key is a unique non-clustered index in a database table by default.

4. Null Values

A Primary Key cannot accept NULL values in a database table whereas a Unique Key can accept only one NULL value in the table.

5. Limit

There can only be one and only one Primary Key on a table, however, there can be multiple Unique Key for a table in a database system.

Primary Key vs. Unique Key

Primary Key	Unique Key
A primary key is used to uniquely identify a record/row in a database table.	A unique key is used to uniquely identify all possible rows in a table and not only the currently existing rows.
It does not accept NULL values.	It can accept only one NULL value in a table.
It is a clustered index by default which means data is organized in the clustered index sequence.	It is a unique non-clustered index by default.
There can be only one primary key in a table.	A table can have multiple unique keys.
The primary key is defined by using the PRIMARY KEY constraint.	The unique key is represented using a UNIQUE constraint.
Used to identify a row in a table.	Used to prevent duplicate values in a column.
Primary key values cannot be changed or deleted.	Unique key values can be modified.

The article was published on March 18, 2017 @ 9:30 AM