Difference between a primary and unique key

Primary Keys
The main purpose of the primary key is to provide a means to identify each record in the table.


A primary key has the following characteristics:
There can only be one primary key for a table.
The primary key consists of one or more columns.
The primary key enforces the entity integrity of the table.
All columns defined must be defined as NOT NULL.
The primary key uniquely identifies a row.
Primary keys result in CLUSTERED unique indexes by default.


---------------------


Unique Keys
A unique key can be used to ensure rows are unique within the database.

Consider the Employee table below:

Assuming that EmployeeID is the primary key, then we may want to place a unique constraint on Government-Number to ensure each employee has their own number.

In SQL Server the unique key has the following characteristics:
There can be multiple unique keys defined on a table.
Unique Keys result in NONCLUSTERED Unique Indexes by default.
One or more columns make up a unique key.
Column may be NULL, but on one NULL per column is allowed.
A unique constraint can be referenced by a Foreign Key Constraint.



Primary and Unique Key Comparison