What is the difference between a primary key and a unique index?
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.
- 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.
- 1
A table can have multiple unique indexes.
- 2
Allows one NULL value (unless defined otherwise).
- 3
Ensures no duplicate values in the indexed column(s).
- 4
Does not define row order (non-clustered in InnoDB).
- 5
Used for enforcing uniqueness but not row identity.
- 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.