What is the difference between a UNIQUE key and a PRIMARY key?

Difficulty: 3/10

primary key, unique constraint, index behavior

Difference Between PRIMARY KEY and UNIQUE Key in MySQL

Both PRIMARY KEY and UNIQUE constraints ensure that values in a column (or set of columns) are unique. However, they differ in rules, behavior, and intended use.

1. Key Differences

1
A table can have only one PRIMARY KEY, but it can have multiple UNIQUE keys.
2
PRIMARY KEY columns cannot contain NULL, while UNIQUE key columns can contain one NULL value (per column).
3
PRIMARY KEY is used to uniquely identify rows; UNIQUE keys enforce uniqueness but are not required for identification.
4

2. When to Use Each

1
Use PRIMARY KEY for the main identifier of the row.
2
Use UNIQUE when a column must be unique but is not the main identifier (e.g., email, username, phone number).

Example: PRIMARY KEY vs UNIQUE Key

In summary, every PRIMARY KEY is unique, but not every UNIQUE key is a primary key. PRIMARY KEY identifies the row; UNIQUE simply prevents duplicates.

Scenario Questions

0-2 years experience

Follow-up Questions