What is the difference between a primary key and a unique index?

Difficulty: 5/10

primary key, unique index, InnoDB storage

Difference Between Primary Key and Unique Index in MySQL

Both PRIMARY KEY and UNIQUE indexes enforce uniqueness in MySQL, but they differ in purpose, behavior, and constraints.

Primary Key

1
A table can have only one primary key.
2
Does not allow NULL values.
3
Uniquely identifies each row in the table.
4
Automatically creates a UNIQUE index.
5
In InnoDB, the primary key is the clustered index, determining the physical row storage order.

Unique Index

Key Differences

1
Primary key = main identifier for each row; Unique index = ensures uniqueness but not identity.
2
Primary key cannot be NULL; unique index can contain NULL.
3
Only one primary key per table; many unique indexes allowed.
4
Primary key defines clustering (InnoDB); unique index does not.

In summary, a PRIMARY KEY is stricter and used to represent table identity, while a UNIQUE index simply enforces uniqueness on one or more columns.

0-2 years experience