Difference between Primary Key and Unique Key

Estimated study time: 8 minutes. Two constraints that look similar but behave differently.

Both a primary key and a unique key enforce uniqueness on a column, which is exactly why they're so often confused. The differences are small but important.

NULL Handling

A primary key column cannot contain any NULL values. A unique key allows a single NULL value in most database systems (SQL Server allows only one, since multiple NULLs would otherwise be considered duplicates under a unique index).

Number Allowed Per Table

A table can have only one primary key, but it can have multiple unique keys.

CREATE TABLE Users (
  UserID INT PRIMARY KEY,
  Email VARCHAR(100) UNIQUE,
  Username VARCHAR(50) UNIQUE
);

Indexing

A primary key automatically creates a clustered index by default (unless one already exists). A unique key creates a non-clustered index by default.

Purpose

The primary key identifies each row as the "main" identity of the record. Unique keys enforce business rules — like ensuring no two users share the same email — without necessarily being the row's primary identifier.

💡 Tip: If a column needs to allow one NULL value while still being unique, it must be a unique key — a primary key will never permit that.

Quick Comparison Table