Explain Primary, Composite & Alternate Keys in Databases

16 / 66

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.

Keys are how a relational database identifies rows and connects tables, and the vocabulary around them — candidate, primary, composite, alternate — is a frequent source of interview questions precisely because the distinctions are easy to blur. The starting point is the candidate key: any minimal set of columns whose values uniquely identify a row. A table can have several candidate keys; you choose one to be the primary key, and the rest become alternate keys.

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

The primary key is the row's canonical identity. There is exactly one per table, it cannot be null, and it is what foreign keys in other tables reference. It can span multiple columns — a composite key — when no single column is unique on its own, as in a join table keyed by (student_id, course_id). The remaining candidate keys are alternate keys, enforced with UNIQUE constraints so they too guarantee uniqueness even though they are not the chosen identity — a users table might have id as the primary key and a UNIQUE email as an alternate key. In most databases the primary key also determines physical storage: it backs the clustered index in engines like InnoDB and SQL Server, so rows are stored in primary-key order, which makes the choice of primary key a performance decision as well as a modelling one.

(student_id, course_id)UNIQUEusersid

The design debate interviewers like is natural versus surrogate keys. A natural key uses real-world data (an email, an ISBN) as identity, which is meaningful but risky because real-world values change and that change must cascade everywhere the key is referenced. A surrogate key is a system-generated, meaningless identifier — an auto-increment integer or a UUID — that never changes, decoupling identity from mutable data; the trade-off is auto-increments leak ordering and don't suit distributed inserts, while UUIDs are globally unique but larger and, if random, can hurt clustered-index locality. The common recommendation is a stable surrogate primary key plus UNIQUE constraints on the natural keys.

UNIQUE