schema design
Primary, Composite & Alternate Keys
A table has exactly one primary key (which can span multiple columns); other uniqueness constraints are "alternate keys" enforced by UNIQUE — the primary key is the row's canonical identity for foreign keys and the clustered index.
Several columns in a table might uniquely identify a row. You pick one to be the identity. The vocabulary describes the rest.
A driver's license. You have exactly one license number — that's your canonical identity to the DMV, police, banks. You also have a unique email, a unique phone, a unique fingerprint — all uniquely identify you, but only the license is the identity the rest of the system references.
Key Concepts
1
Four words, one example. A users table with id, email and phone, where all three are unique:
usersidemailphone
2
- **Candidate key** — any minimal set of columns that identifies a row uniquely. Here there are three:
`id`, `email`, `phone`.
- **Primary key** — the candidate you chose as the row's identity. Exactly one per table, never null,
and what other tables point at.
- **Alternate key** — the candidates you did not choose, still enforced with `UNIQUE`.
- **Composite key** — a key made of more than one column. A `enrollments` table keyed by
`(student_id, course_id)`, because neither alone is unique.idemailphoneUNIQUEenrollments
3
"Minimal" is load-bearing. (id, email) is unique but not a candidate key, since id alone already is —
the extra column adds nothing.
(id, email)id
4
The primary key usually decides physical layout. In InnoDB and SQL Server the table is stored in
primary-key order — a clustered index. Rows near each other in key order are near each other on disk.
5
That makes the choice a performance decision. A sequential id appends each new row at the end: one hot
page, tight packing. A random UUID scatters inserts across the whole table, so every insert touches a
different page and pages split.
id
6
On a large table that difference is measurable. It is the main argument against random UUIDs as a primary
key, and why time-ordered UUIDs such as UUIDv7 exist.
7
Natural versus surrogate. A natural key is real-world data used as identity: an email, an ISBN.
A surrogate key is a meaningless generated value: an auto-increment integer, a UUID.
8
The problem with natural keys is that real-world values change. If email is your primary key and it is
referenced by five tables, changing an address means updating every one of them. Worse, people share
phone numbers, companies reissue employee codes, and "surely this is unique" is wrong more often than
expected.
email
9
The usual answer. A stable surrogate primary key, plus UNIQUE constraints on the natural keys. You
get an identity that never changes and still forbid duplicate emails.
UNIQUE
10
The trade with surrogates is real, though: auto-increment ids leak information — order 5,000 today and
order 5,400 next week tells a competitor your volume — and they need coordination when several machines
insert at once. That is the case for UUIDs, at the cost of size and, if random, the clustering problem
above.
11
What the interviewer is probing.1. "Natural or surrogate primary key?" Probing: the trade. Stalls: "Natural is cleaner."
Moves up: surrogate keys are stable and narrow, which matters because a natural key can change;
natural keys avoid an extra lookup and are self-describing.
12
2. "Why avoid a wide composite primary key?" Probing: the propagation. Stalls: "It is fine."
Moves up: every secondary index and foreign key carries it, so storage and index size multiply.
13
3. "What is an alternate key?" Probing: the vocabulary. Stalls: "A backup key." Moves up:
another column set that is unique and could have been the primary — enforced with a unique
constraint.
14
4. "What is the risk of a UUID primary key?" Probing: the index behaviour. Stalls: "None."
Moves up: random UUIDs scatter inserts across the index, causing page splits and poor locality —
time-ordered variants fix it.