Primary Keys: Surrogate vs Natural

Primary keys give a row a stable identity

Every table needs one honest answer to "which row is this?" A PRIMARY KEY is that answer: the column (or set of columns) the database uses to identify a row. It enforces a contract you can build on. No two rows share a primary key value, and the key is never NULL. That guarantee is what makes "one row per customer" actually true, and it is what every join and foreign key relies on. Get identity wrong and a nightly load can silently create two rows for the same customer, so every downstream count drifts.

You choose what the key is. There are two families.

• Natural key: a business value that is already unique (an email, an ISBN, a country_code). It is meaningful, but business values change (people change emails), get reused, and are often wide text, which makes joins and indexes slower.
• Surrogate key: a system-generated integer with no business meaning, usually auto-assigned. It never changes, is compact, and joins fast.

Data engineers prefer a surrogate primary key for warehouse dimensions and keep the natural key as a separate attribute (often UNIQUE). Identity rides on the surrogate; the business value is just data.

Worked example

CREATE TABLE dim_customer (
    customer_sk  INTEGER PRIMARY KEY,   -- surrogate: system identity
    email        TEXT UNIQUE,           -- natural key kept as an attribute
    country_code TEXT
);

INSERT INTO dim_customer (email, country_code) VALUES ('ada@example.com',   'GB');
INSERT INTO dim_customer (email, country_code) VALUES ('grace@example.com', 'US');
SELECT customer_sk, email FROM dim_customer;
-- 1|ada@example.com
-- 2|grace@example.com

In SQLite, INTEGER PRIMARY KEY is special: that column becomes the table's rowid, so an insert can omit it and SQLite fills in the next integer. The table is physically stored in rowid order, so lookups and joins on that key are fast with no extra index. Every other PRIMARY KEY and every UNIQUE column instead gets an automatic unique index created behind the scenes.

Pitfalls

Do not promote a natural key to the primary key just because it looks unique today. The day it is not (a supplier reuses a SKU, a customer re-registers an email), the constraint rejects a legitimate load and your pipeline breaks. Use a surrogate PRIMARY KEY and add UNIQUE on the natural key. You get stable identity and a duplicate guard, decoupled. Inserting a row that collides with a UNIQUE value raises a constraint error; INSERT OR IGNORE tells SQLite to skip just that row instead of aborting the whole script.

UNIQUE still allows many NULLs. In SQL, NULL is never equal to NULL, so a UNIQUE column accepts any number of NULL rows. email TEXT UNIQUE does not force an email to exist. If a business key must always be present, add NOT NULL alongside UNIQUE.

Interview nuance: the reason DEs reach for surrogate keys is stability under change. When a customer edits their email, you update that one attribute and the customer_sk stays the same, so every fact row already pointing at that surrogate stays valid. If identity rode on the email, changing it would orphan or force a rewrite of every referencing row. Surrogate keys decouple identity from attributes, which is exactly what slowly changing dimensions depend on.

Execution mode: you write a multi-statement script against a fresh in-memory SQLite database. Hidden assertions then check the primary key, the unique constraint, and the row counts.