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
PRIMARY KEY automatically creates a clustered index (in InnoDB), while UNIQUE creates a secondary index.

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.

Difficulty: 3/10

Topics: primary key, unique constraint, index behavior

Scenario Questions

0-2 years experience

1
You need to add a column to a MySQL table that must be unique but also allow NULL values. How would you define it, and why would you choose a UNIQUE key over a PRIMARY key?
2
If you create a table with both a PRIMARY KEY and a UNIQUE key on the same column, what does MySQL enforce, and what happens if you try to insert duplicate values?
3
What happens if you try to define a PRIMARY key on a column that already has a UNIQUE index? Explain the effect on indexes.

2-5 years experience

1
During a code review you notice duplicate email addresses are being inserted despite a UNIQUE constraint. The table also has a PRIMARY KEY on id. Walk me through how you would debug this issue.
2
You are designing a user table that will be sharded later. Explain the trade‑offs of using a composite PRIMARY KEY versus a separate UNIQUE key for the email column.
3
Our application sometimes needs to insert rows without specifying the primary key value, relying on AUTO_INCREMENT. How does MySQL treat the UNIQUE constraint on another column in this scenario?

5-8 years experience

1
Our service experiences high write throughput and we notice contention on the primary key index. Would switching the email column from a UNIQUE key to a PRIMARY KEY help, and what are the performance implications?
2
We need to migrate a legacy table that currently uses a UNIQUE key as the natural identifier to a new schema where that column becomes the PRIMARY KEY. What steps and risks would you consider?
3
Explain how MySQL’s handling of NULLs differs between UNIQUE and PRIMARY keys, and how that impacts data integrity in a multi‑tenant SaaS database.

8+ years experience

1
We are consolidating several micro‑services databases into a single MySQL cluster. How would you decide whether to keep existing UNIQUE constraints or promote them to PRIMARY keys to simplify cross‑service joins and replication?
2
In a long‑term roadmap, we plan to move from MySQL to a distributed SQL system that doesn’t support composite primary keys the same way. How would you redesign our schema’s UNIQUE vs PRIMARY key usage to minimize migration pain?
3
Discuss the operational trade‑offs of relying on PRIMARY keys for data deduplication versus using UNIQUE indexes, especially regarding backup/restore and point‑in‑time recovery.

Follow-up Questions

❓Can you describe how MySQL treats NULL values in a UNIQUE column versus a PRIMARY KEY column?
❓What impact does the choice between PRIMARY and UNIQUE have on index size and query performance?
❓How would you decide which to use when modeling a natural identifier versus a surrogate key?