Table of Contents Link to heading

• The Primary Key Selection Problem
• Rule 1: Keep the Primary Key as Small as Possible
• Rule 2: Choose Stable Attributes
• Rule 3: Prefer Natural Keys Where They Aid Readability
• Rule 4: Fall Back to a Surrogate Key When Necessary

The Primary Key Selection Problem Link to heading

When a relation has multiple Candidate Keys, only one can be designated as the Primary Key. The others become Unique Key constraints. The choice of PK has downstream consequences throughout the entire schema — the PK value is copied into child tables as a Foreign Key every time a relationship is established. A poor PK choice creates performance, maintenance, and integrity problems that compound as the database grows.

The following rules, applied in order, guide the selection decision.

Rule 1: Keep the Primary Key as Small as Possible Link to heading

The PK value is duplicated as a Foreign Key in every related child table. In a large database, thousands or millions of copies of each PK value will exist across the schema. The larger the PK, the more bytes the DBMS must load into memory to perform joins and lookups.

• Bad: PK(studentName, dateOfBirth, streetAddress) — three attributes, many bytes, all of which appear in every FK reference
• Good: PK(studentID) — a single small integer

As a practical guideline, keep Primary Keys to one or two attributes where possible. Three attributes is acceptable; more than that is almost always a sign that a Surrogate Key should be introduced instead.

Rule 2: Choose Stable Attributes Link to heading

The PK is the reference point that Foreign Keys in child tables point to. If a PK value changes, every FK that references it must also be updated — across potentially many tables and many rows. This cascade of updates is both operationally expensive and a source of referential integrity risk.

Choose a PK whose value has no reason to change for the lifetime of the record:

• Bad: PK(studentName, streetAddress) — names change after marriage; addresses change on every move
• Bad: PK(mobilePhone) — phone numbers are reassigned and change frequently
• Good: PK(studentID) — an assigned identifier with no inherent meaning that could cause it to change

The best PKs are values that are assigned once and never updated — either system-generated identifiers or real-world codes that are stable by design (ISBNs, tax file numbers, registration numbers).

Rule 3: Prefer Natural Keys Where They Aid Readability Link to heading

Where a compact, stable Natural Key exists, it can improve the readability of query results and the transparency of data relationships compared to a Surrogate Key.

Abbreviated codes are particularly effective:

• st for Street, rd for Road, ave for Avenue — in a StreetType lookup table, streetTypeCode as PK is more readable in query output than streetTypeID = 1
• Airport codes (SYD, MEL, LAX) as PKs in a flight system are immediately interpretable
• ISO country codes (AU, US, GB) are more meaningful FKs than integer IDs

The benefit is human readability in query results and reports. The trade-off is that natural codes must be managed carefully to ensure they remain unique and do not change.

Rule 4: Fall Back to a Surrogate Key When Necessary Link to heading

When no Candidate Key satisfies the criteria above — all are too large, too unstable, or too complex — use a Surrogate Key.

A Surrogate Key is a system-generated value (typically an auto-incrementing integer or UUID) with no business meaning. It is stable by definition (it never needs to change), compact (a single integer), and unique (generated by the DBMS).

CREATE TABLE Student (
    studentID   INT          IDENTITY(1,1) PRIMARY KEY,
    email       VARCHAR(200) NOT NULL UNIQUE,
    studentName VARCHAR(100) NOT NULL
);

studentID here is the Surrogate Key. The natural identifier (email) is still present and constrained as Unique — it just isn’t the PK. This is the correct pattern: the Surrogate Key handles identity and referential integrity; the Natural Key still enforces real-world uniqueness.