Surrogate keys
A Socratic walk-through of surrogate keys — reasoned out one step at a time, not lectured.
The question we started with
THE QUESTION #Why does a sales record point at a number that means nothing rather than at the customer's own identifier?
A customer already has an identifier. It is printed on their card, quoted in emails, typed by the call centre: ACME-0041. It is unique, it is known to everyone, and it is right there. Yet the sales table does not store it. It stores customer_key = 88213, a number that appears nowhere in the business and means nothing to anybody.
That looks like gratuitous indirection — an extra join to learn a fact you already had. What is the number buying that is worth the cost of not being able to read the row?
Reasoning it through
REASONING #Ask first what a key is being asked to do. In the sales table it does exactly one job: it points, unambiguously and forever, at one row in the customer table. Nothing else. It is not there to be read, printed, sorted, or reasoned about. It is an arrow.
Now ask what ACME-0041 is for. It is a business identifier. It exists to be communicated between humans and systems, and because it exists in the business, it is subject to the business's decisions. Consider a few, none of them exotic. Acme is acquired and re-registered under the parent's numbering scheme. The identifier scheme runs out of digits and is widened. Two records turn out to be the same firm and are merged. A branch is recoded when the region is reorganised. In every one of these, an identifier that was correct and unique yesterday changes today.
And now the crucial question: what happens to ten years of sales rows that pointed at the old value? If they hold the business identifier directly, they now point at nothing, or worse, at whatever entity later inherits the reused code. You must find and rewrite every one of them, in every table, consistently, or the history silently detaches from its customer.
So the reasoning arrives at a rule that is really about authority. A key that some other party may change is a key you do not control. Making it the anchor of your references means outsourcing the stability of your entire fact history to whoever administers that scheme — and they never promised you stability, because their identifier was never intended for that job.
A surrogate key is simply an identifier you mint yourself, meaning nothing to anyone, precisely so that nothing in the world can ever require it to change. Its meaninglessness is not a side effect to be tolerated; it is the whole feature. There is no fact about the customer that could make 88213 wrong, because it asserts no facts.
Notice what this makes possible. The business identifier does not disappear — it moves. It becomes an attribute of the customer row, where it can be updated in one place, on one row, when Acme is re-registered. A single update, and ten years of sales rows are still correct, because they never pointed at the identifier; they pointed at the customer.
There is a second use, less obvious but at least as important. If the customer table keeps history — a new row each time the customer's details change — then the business identifier is no longer unique in that table at all. ACME-0041 might name four rows, one per era. Something has to distinguish them, and a surrogate key does it naturally: each version gets its own. A sale then points not merely at Acme, but at Acme as it was recorded when the sale happened. The surrogate key has quietly become the mechanism that makes historical accuracy possible.
An honest counterweight, because this is not a rule without cost. Every lookup by business identifier now requires a join, and debugging a table of opaque integers is genuinely worse than reading meaningful codes. Some data stores handle joins poorly enough that people denormalise the identifier back in alongside the key. And where an identifier really is stable and externally governed — an ISO country code, say — insisting on a surrogate can be ceremony rather than protection. The judgement is about who controls the value and whether it can change, not about a blanket preference.
The analogy
THE ANALOGY #Think of a library assigning each book an accession number when it arrives, rather than filing everything by ISBN. The ISBN is meaningful, printed on the book, and useful for ordering copies. But editions get reissued, publishers reassign, and a donated volume may predate ISBNs entirely — whereas the accession number, invented by the library and meaning nothing beyond "the 88,213rd item we acquired", can never be made wrong by anything a publisher does. Loan records point at the accession number, and the ISBN lives in the catalogue entry where it can be corrected.
an accession number is at least stamped inside the physical book, so a librarian holding the object can recover it, whereas a surrogate key exists only inside the database — lose the mapping table and the number becomes genuinely unrecoverable rather than merely inconvenient.
Clarifying the model
THE MODEL #Three refinements.
First, a common misconception: that surrogate keys mean the business identifier is untrusted or unimportant. It is neither. It is the identifier for every human and every external conversation. The claim is narrower — that it should not be the thing rows point at, because pointing requires immutability and the business never guaranteed it.
Second, the surrogate must be genuinely meaningless to do its work. A key encoding the region or the year is not a surrogate; it is a business identifier wearing a numeric costume, and it will need to change when the region is reorganised, which is exactly the failure it was meant to avoid.
Third, the choice between a sequence and a random identifier such as a UUID is a separate question, not part of this one. Both are surrogates. Sequences are compact and order-revealing, which is sometimes useful and sometimes a leak; random identifiers can be generated independently by several systems without coordination, at the cost of size and index locality. Either satisfies the property that matters here.
A picture of it
THE PICTURE #How to readfollow the crow's-foot lines, which mean many at the branching end. One customer row is referenced by many sales, and the reference is the minted key rather than the code. Read the attribute comments as the argument in miniature: everything in the customer box except the key can be changed by somebody else, and the third entity is that somebody — it supplies the business identifier and reserves the right to alter it. Because no line ends on business_id, none of those changes reaches a sale.
What became clearer
WHAT CLEARED #The indirection is not about efficiency or convention. It is about who is allowed to change the value a reference depends on. A business identifier belongs to the business and can be reissued, merged, or rewidened without anyone consulting your database; a surrogate key belongs to the database and asserts nothing that the world could falsify. Making references point at the meaningless number turns an identifier change from a mass rewrite of history into an update of one attribute on one row — and, where the customer table keeps versions, gives each version something to be pointed at individually.
Where to go next
ONWARD #- How a customer table keeps several rows for one business identifier, and what marks which one is current.
- Matching a fact to the version of an entity that was current when the fact occurred.
- When a natural key really is safe to reference, and how to tell.
Key terms
TERMS #| Term | What it means |
|---|---|
| Surrogate key | an identifier minted by the storing system, carrying no business meaning, used as the target of references. |
| Natural or business key | an identifier that already exists in the business, such as a customer code or an ISBN. |
| Referential stability | the property that a reference keeps pointing at the same entity regardless of changes to that entity's attributes. |
| Dimension table | a table describing an entity, such as a customer, that fact rows refer to. |
Every term the collection defines is gathered in the glossary.