Why I Default to Surrogate Keys Over Natural Keys

A natural key looks like a free lunch: the data already carries a unique identifier, so why generate another one? The bill arrives the day that identifier changes — and in regulated data, it always eventually changes.

This is a decision I make almost reflexively now, so it is worth writing down why. When I design a table, the primary key is a surrogate — a system-generated value with no business meaning — and the business identifier lives beside it under a unique constraint. That gives me referential stability from the surrogate and business-level uniqueness from the constraint, without having to choose between them. The rest of this note is the failure cases that taught me to default to it, and the narrow spots where a natural key genuinely earns the primary-key slot.

The two things a key has to do

A primary key does two jobs that people tend to conflate. It is the anchor other rows point at through foreign keys, and it is often also the thing that enforces business uniqueness — that there is only one counterparty with this Legal Entity Identifier, one employee with this national insurance number, one instrument with this ISIN. A natural key tries to do both jobs with one column. That works right up until the two jobs pull in opposite directions, which they do the moment the business value has to change.

The fix is to split the jobs. Let a surrogate be the anchor and let a unique constraint enforce the business rule. Both goals are met, and each is free to change without disturbing the other.

The failure cases

Business identifiers change. This is the one people underestimate. A tax number gets reissued when a country restructures its scheme. An LEI lapses and the entity re-registers under a restructured group. A VAT number changes on reincorporation. An email address — beloved as a “natural” user key — is edited by the user on a Tuesday afternoon. None of these are exotic. They are the normal life of reference data in a regulated firm, and every one of them turns a natural primary key into a liability.

Cascading updates are brutal. If the natural value is the primary key and twelve child tables reference it, changing one identifier means an ON UPDATE CASCADE that rewrites rows across all twelve, taking locks and bloating write-ahead log as it goes. On a large estate that is a maintenance-window job for something the business thinks of as a one-field correction. With a surrogate, the same correction is a single-row UPDATE of the unique column, and nothing downstream moves because nothing downstream ever pointed at the business value.

Foreign keys get wide. Natural keys are frequently composite — an order line keyed by (order number, product code, warehouse). Make that the primary key and every child table carries all three columns as its foreign key, indexes them all, and joins on all three. A single bigint or uuid surrogate is eight or sixteen bytes propagated everywhere instead, with narrower indexes and simpler joins. Multiply that across a busy schema and it is a real difference in storage and cache behaviour, not a theoretical one.

You often need the row before the natural value exists. Staged imports, records created in a draft state, rows inserted in the same transaction that will later receive their official identifier from an external system — all of these need something to reference now. A surrogate exists the instant the row does. A natural key that is still NULL cannot anchor anything.

Natural keys leak, and often that leak is personal data. If your primary key is a national insurance number, that number is now copied into every child table’s foreign key and every index that references it. You have spread a piece of directly identifying data across the schema for purely structural reasons. A surrogate keeps the sensitive value in one place, under one constraint, where you can govern it.

The pattern in PostgreSQL 17

Here is the shape I reach for. The surrogate is a standard-SQL identity column — prefer GENERATED ALWAYS AS IDENTITY over the old serial pseudo-type, which is now discouraged because it leaves the sequence loosely coupled to the column. The natural key sits alongside as a NOT NULL UNIQUE constraint.

-- PostgreSQL 17
CREATE TABLE counterparty (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    lei         char(20) NOT NULL,        -- natural key: Legal Entity Identifier
    legal_name  text     NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT counterparty_lei_key UNIQUE (lei)
);

-- Children reference the surrogate, never the LEI
CREATE TABLE trade (
    id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    counterparty_id  bigint  NOT NULL REFERENCES counterparty (id),
    notional         numeric(18,2) NOT NULL,
    booked_at        timestamptz NOT NULL DEFAULT now()
);

-- The entity restructures and its LEI is reissued.
-- One row changes. Nothing in trade moves, because nothing
-- in trade ever pointed at the LEI.
UPDATE counterparty
   SET lei = '529900T8BM49AURSDO55'
 WHERE id = 42;

The unique constraint still guarantees you cannot have two counterparties with the same LEI — the business rule is intact. What you have given up is nothing; what you have gained is that the identifier is now free to change without a schema-wide rewrite. That is the whole trade.

If you prefer an opaque key across distributed writes, swap the identity for a uuid defaulting to gen_random_uuid(), which is built into core PostgreSQL. Be aware that on version 17 this produces a random (v4) UUID, whose lack of ordering fragments the primary-key index on insert. The time-ordered uuidv7() generator that addresses this landed in core in PostgreSQL 18, not 17, so on 17 you either accept v4 or generate v7 in the application. For most single-cluster schemas a bigint identity is smaller, faster to index, and perfectly sufficient; reach for UUIDs when you genuinely need keys minted outside the database.

When the natural key really is the right primary key

Defaults have exceptions, and pretending otherwise is how you end up cargo-culting surrogates onto tables that do not need them. Two cases where I keep the natural key as the primary key:

Pure junction tables. A link table joining two entities is best keyed by the composite of the two foreign keys — say (portfolio_id, instrument_id). Those foreign keys already point at surrogates, so the composite is stable by construction, and it enforces exactly the rule you want: one row per pairing. Adding a separate surrogate here buys nothing and lets duplicate pairings slip in unless you also add the unique constraint anyway.

Small, externally governed, genuinely immutable code tables. ISO 3166 country codes, ISO 4217 currency codes, and similar. The code is short, single-column, stable by international agreement, and the table is tiny. Here the natural key as primary key is readable, keeps the code visible in child rows without a join, and the immutability that makes natural keys dangerous elsewhere actually holds. Even so, treat “genuinely immutable” as a claim to test, not assume — plenty of “standard” code lists have quietly reissued values over the years.

Everything else gets a surrogate primary key and the natural key as a unique constraint. It is not that natural keys are wrong; it is that using one as your primary key bets the stability of your entire referential graph on a business value staying constant, and in regulated data that is a bet you lose. This is the sort of quiet structural choice that shows up years later as data debt — cheap to get right at CREATE TABLE, expensive to unwind once a decade of foreign keys depends on it. If you have ever leaned on a business identifier to make an operation safe to repeat, the same reasoning applies to idempotency keys, and it is worth being deliberate about which identifier you trust. The identifiers that arrive in richer ISO 20022 payment messages are a good reminder of how often an “official” reference is only as stable as the scheme behind it.

Keep the natural key. Constrain it. Just do not make it the thing everything else points at.