Keys: Candidate, Primary, Natural and Surrogate

In one line

A super key is any set of columns that uniquely identifies a row; a candidate key is a minimal one; the primary key is the candidate you choose, and the others become alternate (UNIQUE) keys. Natural keys come from the business (email, ISBN); surrogate keys are generated (bigint, UUID). Most designs use a surrogate primary key plus UNIQUE constraints on the natural keys.

0/3 · 0%

Think of it like this

Identifying a person. Passport number, Aadhaar number, and (name + date of birth + birthplace) might each identify them. Those are candidate keys; a hospital still gives them its own patient number (a surrogate) because the others change, are missing, or are sensitive.

Key ideas

01
Super key: any column set that is unique ({id, email} is a super key). Candidate key: a super key with nothing removable ({id}, {email}). Composite key: a key of more than one column ((order_id, line_no)).
02
Natural keys carry meaning and can change (people change email; countries rename), may be long (joins and every index copy them), and may be sensitive (national IDs). Surrogate keys are stable, compact and meaningless.
03
Rule of thumb: surrogate bigint or UUID primary key, plus UNIQUE on each natural key so the business rule is still enforced. Pure junction tables are the exception: their composite natural key (student_id, course_id) is ideal.
04
Foreign keys reference a primary or unique key and enforce referential integrity: no child can point to a missing parent. They also document the model for every reader of the schema.
05
In PostgreSQL the primary key creates a unique B-tree index; tables are heaps, so the key does not determine physical order. In MySQL InnoDB the primary key is the clustered index, so key choice affects insert locality and secondary index size (each secondary index stores the PK).

Code & diagrams

keys.sqlsql

CREATE TABLE app_user (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- surrogate
  email      citext NOT NULL UNIQUE,                            -- natural, alternate key
  username   text   NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_line (
  order_id   bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  line_no    int    NOT NULL,
  product_id bigint NOT NULL REFERENCES product(id),
  quantity   int    NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_no)                                -- composite key
);

Interview problem

The problem

Natural or surrogate key for a product catalogue

Products have a SKU from the ERP system, and the business says "SKUs never change". Should sku be the primary key referenced by orders, carts and reviews?

When it breaks

Email as the primary key

What you see

A user changing email requires updating the PK and every referencing row across services; logs, events and caches keyed by the old email break.

Fix & prevent

Surrogate PK plus a unique (case-insensitive) email; reference users by ID everywhere.

Explain it without notes

What is the difference between a candidate key and a super key?

Practice

List the candidate keys of employee(id, email, national_id, name, dept) and choose the primary key.

Trade-offs

↔
Surrogates add a column and an extra unique index, and in return keep identity stable and joins compact.

Done when you can

I can identify candidate keys and justify natural vs surrogate primary keys.

Command Palette