SQL / DATA MODELLING AND NORMALISATION
Surrogate versus natural keys
Pick between a natural and surrogate primary key by testing stability, and guard a surrogate with a UNIQUE constraint on the natural key.
What you will learn
- Add a UNIQUE constraint on the natural key whenever the primary key is surrogate
- Test candidate keys for immutability, not only for uniqueness and NOT NULL
- Use the pair of foreign keys as the primary key of a junction table
- Keep short standardised codes like ISO currency codes as natural keys
Understanding Surrogate versus natural keys
A natural key is made of columns that already carry business meaning and that the outside world uses to identify the thing: an ISBN for a book edition, an ISO 4217 code for a currency, a (student_id, course_id) pair for an enrolment. A surrogate key is a value the database invents for its own convenience, an auto-filled integer or a UUID, and it means nothing outside the table it lives in. Both satisfy the formal requirements of a primary key, so the choice is not about relational correctness but about who controls the value and how likely that value is to change.
It helps to separate the two jobs a key does. One job is to be a handle: something narrow and unchanging that foreign keys point at, indexes are built on, and joins compare. The other job is to state the real-world uniqueness rule, the claim that two rows with the same ISBN describe the same edition and must not both exist. A surrogate key is excellent at the first job and useless at the second, so declaring one as the primary key does not delete the rule, it just means you have to write the rule yourself as UNIQUE (isbn13).
The reason surrogate keys dominate in practice is that a foreign key stores a copy of the parent's key value in every child row. When the key is business data, the business eventually changes it, a supplier is renamed or a mistyped email is corrected, and that change has to reach every referencing row and every index containing it. Surrogate keys are not free either: they add a column, force a join whenever you want a human-readable label, and, if you forget the UNIQUE constraint, they silently permit two rows for the same entity because those rows really do have different primary keys.
CREATE TABLE editions_bad (
edition_id INTEGER PRIMARY KEY,
isbn13 TEXT NOT NULL,
title TEXT NOT NULL
);
CREATE TABLE editions_good (
edition_id INTEGER PRIMARY KEY,
isbn13 TEXT NOT NULL UNIQUE,
title TEXT NOT NULL
);
INSERT INTO editions_bad (isbn13, title) VALUES
('9780262033848', 'Introduction to Algorithms'),
('9780262033848', 'Introduction to Algorithms');
INSERT OR IGNORE INTO editions_good (isbn13, title) VALUES
('9780262033848', 'Introduction to Algorithms'),
('9780262033848', 'Introduction to Algorithms');
SELECT 'editions_bad' AS tbl, COUNT(*) AS row_count, COUNT(DISTINCT isbn13) AS distinct_isbn
FROM editions_bad
UNION ALL
SELECT 'editions_good', COUNT(*), COUNT(DISTINCT isbn13)
FROM editions_good
ORDER BY tbl;A surrogate primary key gives you a stable handle for rows but says nothing about real-world identity, so the natural key still has to be declared as a UNIQUE constraint.
Worked examples
A natural key that the business renames
Shows how changing a natural key value rewrites every child row that stores a copy of it.
PRAGMA foreign_keys = ON;
CREATE TABLE suppliers (
supplier_code TEXT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE parts (
part_id INTEGER PRIMARY KEY,
supplier_code TEXT NOT NULL REFERENCES suppliers(supplier_code) ON UPDATE CASCADE,
part_name TEXT NOT NULL
);
INSERT INTO suppliers VALUES ('ACME', 'Acme Tools');
INSERT INTO parts (supplier_code, part_name) VALUES
('ACME', 'hex bolt'),
('ACME', 'flange nut');
UPDATE suppliers SET supplier_code = 'ACME-GB' WHERE supplier_code = 'ACME';
SELECT part_id, supplier_code, part_name FROM parts ORDER BY part_id;Example explained
Line 1PRAGMA foreign_keys = ON is required in SQLite, which otherwise parses REFERENCES but enforces nothing.
Line 2Because supplier_code is the key, its value is duplicated into every parts row, so the rename touches two rows here and every referencing row in a real database.
Line 3ON UPDATE CASCADE is what makes the rename possible at all; with the default NO ACTION the UPDATE fails because the child rows would point at a code that no longer exists.
Line 4With a surrogate supplier_id the parts rows would not change, since the code would exist in exactly one place.
Composite natural key on a junction table
Shows the pair of foreign keys acting as the primary key so a repeated enrolment cannot be stored twice.
CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (
student_id INTEGER NOT NULL REFERENCES students(student_id),
course_id INTEGER NOT NULL REFERENCES courses(course_id),
enrolled_on TEXT NOT NULL,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES (1, 'Mina'), (2, 'Otto');
INSERT INTO courses VALUES (10, 'Databases'), (20, 'Logic');
INSERT INTO enrolments VALUES (1, 10, '2026-01-15'), (2, 10, '2026-01-16');
INSERT OR IGNORE INTO enrolments VALUES (1, 10, '2026-02-01');
SELECT student_id, course_id, enrolled_on FROM enrolments ORDER BY student_id, course_id;Example explained
Line 1PRIMARY KEY (student_id, course_id) states the identity rule of the relationship: one row per student per course.
Line 2The last INSERT OR IGNORE is discarded by that key, and the original 2026-01-15 date survives untouched because nothing was updated.
Line 3The columns are surrogate values borrowed from the parents, yet their combination is the natural key of the enrolment itself.
Line 4Adding enrolment_id INTEGER PRIMARY KEY instead would have accepted the third row and double-enrolled Mina with no error.
When the natural key is the better choice
Shows a short, externally standardised code used as the key so reporting queries need no extra join.
CREATE TABLE currencies (
currency_code TEXT PRIMARY KEY CHECK (length(currency_code) = 3),
minor_units INTEGER NOT NULL
);
CREATE TABLE payments (
payment_id INTEGER PRIMARY KEY,
currency_code TEXT NOT NULL REFERENCES currencies(currency_code),
amount_minor INTEGER NOT NULL
);
INSERT INTO currencies VALUES ('EUR', 2), ('JPY', 0), ('USD', 2);
INSERT INTO payments (currency_code, amount_minor) VALUES
('EUR', 2500), ('JPY', 1800), ('EUR', 999);
SELECT currency_code,
COUNT(*) AS payment_count,
SUM(amount_minor) AS total_minor
FROM payments
GROUP BY currency_code
ORDER BY currency_code;Example explained
Line 1ISO 4217 codes are fixed width, non-null and never renamed in place, so they pass the immutability test that a supplier name fails.
Line 2payments stores the code itself, so the grouped report is already readable and never joins to currencies.
Line 3A surrogate currency_id would have forced a join in this query just to print the labels.
Line 4The CHECK on length documents the shape of the key and stops a two-letter country code being inserted by mistake.
Important notes
SQLite fills in INTEGER PRIMARY KEY automatically; PostgreSQL spells this GENERATED ALWAYS AS IDENTITY and MySQL AUTO_INCREMENT, and SQLite enforces foreign keys only after PRAGMA foreign_keys = ON.
A surrogate key is an internal handle, not a privacy feature: sequential integers are guessable, and switching to a UUID fixes that at the cost of a wider key copied into every child row and index.
Common mistakes
Adding a surrogate id and never constraining the natural key, so a rerun of an import stores the same customer twice; joins fan out and SUM totals double with no error raised.
Using an email address, phone number or person's name as the primary key; when the value is corrected, every child table and index holding a copy has to be rewritten, or the UPDATE simply fails on the foreign key.
Trusting that a code from an external system is permanent, such as recycled employee numbers or reissued SKUs, so old child rows silently end up attached to a different entity than they were created for.
Try it yourself
Change, predict, then run
Create airports (airport_id INTEGER PRIMARY KEY, iata_code TEXT NOT NULL, city TEXT NOT NULL), insert 'LHR' twice and use COUNT(*) to confirm both rows were stored. Then recreate the table with UNIQUE on iata_code, repeat the two inserts, and show that only one row survives.
Open the SQL workspaceCheck your understanding
A customers table is defined as customer_id INTEGER PRIMARY KEY plus email TEXT NOT NULL. The import job runs twice and the same person arrives again. What happened, and what would have prevented it?
- The second insert failed, because a primary key prevents duplicate rows.
- Both rows were stored, and a NOT NULL constraint on email would have blocked the second one.
- Both rows were stored, because the generated customer_id values differ; only UNIQUE (email) would have rejected the second one.
- Both rows were stored, and the only fix is to drop customer_id and make email the primary key.
Show answer
The primary key constrains customer_id alone, and the second row was handed a fresh value, so there was no statement of real-world identity for the database to violate; UNIQUE (email) supplies that statement. Promoting email to primary key is tempting because email genuinely is the natural key here, but it also makes every child table store a copy of a value the user is allowed to change, which is the problem the surrogate key was avoiding. NOT NULL only rules out missing values, never repeated ones.