skip to content

What is the difference between a natural primary key and a surrogate primary key, and how do you decide which to use for a table?

level: juniorimportance: must knowfreq 70%

answer

  1. Natural = business data; surrogate = generated, meaningless
  2. Surrogate for stability and narrow width
  3. Always UNIQUE on the natural key too
  4. Join tables: composite of the two FKs
  5. Natural keys are often personal data

basics

~20 s

A natural key is business data that already identifies the row (email, ISBN, country code). A surrogate key is a meaningless generated value (a sequence integer or UUID). Default to a surrogate for stability and narrowness, and keep the natural key as a UNIQUE constraint.

solid answer

~60 s

A natural key comes from the domain and carries meaning - an ISBN, a country code, an email address. A surrogate key is generated by the system purely to identify the row: an auto-increment or sequence integer, or a UUID. Natural keys are appealing because they need no extra column and they make child tables self-describing - a fact table holding a country code may not need to join at all. But domain values change: emails change, product codes get reissued, a "unique" identifier turns out to be reused or entered wrong. When a primary key changes, every foreign key referencing it changes with it. Surrogate keys are stable by construction, narrow, and safe to expose in internal joins. Their cost is an extra column plus the risk of accidentally creating duplicate business rows if you forget the uniqueness constraint on the natural key. The practical rule: surrogate primary key, plus a UNIQUE constraint on the natural key so the business rule is still enforced. Genuine exceptions are pure join tables, where the composite of the two foreign keys is the natural and correct key.

code

sql · 14 lines
sql
CREATE TABLE customer (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email   text NOT NULL,
  name    text NOT NULL,
  CONSTRAINT customer_email_key UNIQUE (email)
);

-- pure join table: the composite natural key is correct
CREATE TABLE enrollment (
  student_id bigint NOT NULL REFERENCES student(id),
  course_id  bigint NOT NULL REFERENCES course(id),
  enrolled_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (student_id, course_id)
);

go deeper

for a junior

Define both terms with examples, state the default (surrogate plus a UNIQUE on the natural key), and give one reason: business values change.

for a middle

Add the propagation cost of a changing key, key width flowing into child tables and indexes, and name the join-table exception.

for a senior

Discuss privacy implications of copying personal identifiers into every child table, clustering effects of composite parent-child keys, and when a stable code table key removes joins worth keeping.

for a principal

Frame it as an identity contract spanning services, caches, exports and partners, and note that the cost of changing the decision later is a full data migration.

## Definitions A **candidate key** is any set of columns that uniquely identifies a row. A **natural key** is a candidate key made of real business data - ISBN for a book, IATA code for an airport, (order_id, line_number) for an order line. A **surrogate key** is a column added solely to identify the row, holding a value with no business meaning: a sequence or identity integer, or a UUID. The **primary key** is whichever candidate key you nominate as the row's identity and the target of foreign keys. ## The case for natural keys - No extra column and no extra index; the identifying value is already there. - Child rows are readable and often self-sufficient. A table keyed by `country_code` lets many queries filter and report without joining back to a lookup table. - Uniqueness of the business value is enforced automatically, because it is the primary key. - Composite natural keys can encode useful clustering: keying an order line by `(order_id, line_no)` stores all lines of an order together, which is exactly how they are read. ## The case for surrogate keys - **Stability.** Business identifiers change more often than people predict. Emails change; product codes get restructured; a national ID is corrected; a supposedly permanent code is retired and reissued to something else. Changing a primary key means updating every referencing row in every child table, plus every index containing it, plus everything outside the database that stored it. - **Narrowness.** A `bigint` is 8 bytes. A natural key that is a 30-character code, or a composite of four columns, propagates its full width into every child table and every index that contains the key. In engines that cluster the table by the primary key, the primary key is also embedded in every secondary index of that table, so width multiplies. - **Uniformity.** Every table identified the same way makes generic tooling - ORMs, audit frameworks, caching layers, replication filters, soft-delete helpers - simple. - **Late binding.** You can create a row before the business identifier is known or verified. - **Privacy.** A natural key is often personal data. Once it is the primary key it is copied into every child table and every log line about those rows, which makes deletion and minimisation obligations much harder. ## The cost of surrogates and how to pay it The main risk is that adding a meaningless key lets you insert two rows that are business-identical, because nothing says otherwise. The fix is mandatory: declare the natural key as a UNIQUE constraint alongside the surrogate primary key. A surrogate without that constraint is how duplicate customers appear. The second cost is an extra join. If a child row holds `country_id` rather than `'FR'`, every report that wants the code must join. For small, stable, genuinely immutable code tables - currencies, countries, ISO-defined statuses - many teams deliberately use the code itself as the key for exactly this reason, and that is a reasonable, defensible choice. ## Where natural keys clearly win - **Pure join tables.** A many-to-many link table between `student` and `course` should be keyed by `(student_id, course_id)`. That composite is stable, enforces the no-duplicate-enrolment rule for free, and adding a surrogate to it buys nothing while allowing duplicates. - **Weak entities keyed by parent plus discriminator.** Order lines, invoice lines, or version rows keyed by `(parent_id, sequence)` get useful physical clustering. - **Small immutable code tables** where the code is externally standardised and never reused. ## Where natural keys usually fail - Email addresses, phone numbers, usernames: user-changeable by definition. - Names of anything: not unique and frequently corrected. - Government identifiers: not universally present, sometimes re-issued, sometimes entered incorrectly, and heavily regulated as personal data. - Vendor or partner codes: owned by someone else, who may restructure them without asking you. ## What a good answer sounds like "Default to a surrogate primary key, always keep a UNIQUE constraint on the natural key so the business rule stays enforced by the database, and make an explicit exception for join tables and for small immutable code tables where the code is the better key." Then be ready to say what specifically goes wrong when a natural key changes: foreign key propagation, index churn, and stale references held outside the database. ## A note on terminology traps A surrogate key is not the same as an auto-increment integer - a UUID is also a surrogate. And "surrogate" says nothing about visibility: whether the key appears in URLs is a separate decision from whether it is generated or natural. Keeping those two questions apart is a marker of a candidate who has actually designed schemas.

  • If you use a surrogate primary key, what must you still do about the natural key, and what happens if you skip it?
    Declare it as a UNIQUE constraint. Without it the table permits two rows that are business-identical - two customers with the same email, two products with the same SKU - because the surrogate makes them distinct rows even though the domain says they are the same entity. Deduplicating afterwards is painful because child rows have already attached to both copies.
  • Name a case where you would deliberately use a natural key as the primary key.
    A pure many-to-many join table, keyed by the composite of the two foreign keys: it is stable, it enforces the no-duplicate-link rule for free, and a surrogate would only permit duplicates. Small externally standardised code tables such as currency or country codes are the other common case, because the code never changes and storing it directly in child rows removes a join from many queries.

A natural key is identifying a person by their phone number; a surrogate key is giving them a membership number. Phone numbers change and get reassigned - the membership number never does, so everything else can safely point at it.

saying these in an interview costs you the question

  • Saying surrogate keys are always right and natural keys are always wrong, including for join tables
  • Adding a surrogate key and omitting the UNIQUE constraint on the business identifier
  • Equating 'surrogate' with 'auto-increment integer' and forgetting UUIDs are surrogates too
  • Choosing an email address or phone number as a primary key because it 'looks unique'
  • Confusing whether a key is generated with whether it is exposed in URLs

context

resolving links…