Why do experienced practitioners insist that a row's primary key value must never change, and how does that argument shape the choice between a natural business key and a surrogate key?

## Why immutability is the requirement Identity is only useful if it is stable. The moment a key value changes, every copy of that value elsewhere is either updated in the same instant or becomes wrong. And a key value is copied a lot: - **Inside the database**: every foreign key column in every child table stores it; grandchildren store it transitively when composite keys are used. - **Inside the storage engine**: in a clustered layout the row is physically located by key, so an update moves the row between pages and rewrites the pointer in every secondary index. - **Outside the database**: URLs and permalinks, caches (which key entries by id), message payloads already published to a queue, change-data-capture streams, the analytics warehouse, log lines, exported CSVs, and third-party systems that stored your id in their own record. The database can be made to handle the first two — a declared referential action that cascades updates rewrites child rows automatically. Nothing handles the third. A cache entry, a published event, a partner's record: those hold the old value forever. So "we can just cascade the update" solves the smallest part of the problem. There is a cost argument too. A cascading key update takes locks on every referencing row across every referencing table, in one transaction. For a widely referenced parent that is a long, blocking, replication-lagging statement — and it may take a schema-shaped path nobody predicted when the key was chosen. ## Natural keys and the mutability trap A **natural key** is a business value that is already unique: email address, national identifier, ISBN, SKU, IATA airport code, ISO country code, username, order number from an upstream system. Using one as the primary key is appealing — no extra column, no extra join to read the meaningful value, and duplicates are impossible by construction. The trap is that these values change more often than intuition suggests: - People change email addresses and usernames. - Identifiers get corrected after a data-entry error — an unglamorous but by far the most common cause. - Codes get reissued or restructured: country codes change, product SKUs are renumbered after a catalogue migration, an ISBN is reassigned. - Systems merge, and two previously distinct namespaces collide, forcing a prefix or renumbering. - Values that were "unique by policy" turn out not to be — the classic being a national id that is duplicated or absent for some people. Every one of these is a rewrite of the identifier across the schema and every downstream consumer. ## The surrogate key answer A **surrogate key** is a value with no business meaning: a sequence or identity value, or a UUID. Because it means nothing, nothing can invalidate it: there is no external event that makes id 4711 the wrong number. Foreign keys reference it, and it never changes. Critically, choosing a surrogate key does **not** mean abandoning the business rule. Add a unique constraint on the natural key columns. Now: - Identity is stable and narrow, and references are cheap. - "One user per email" is still enforced by the engine. - The email can be updated with a single-row UPDATE, because nothing references it. That is the whole trick: separate *identity* (immutable, internal, meaningless) from *business uniqueness* (mutable, meaningful, still enforced). ## When a natural key is still fine Small, stable, externally governed code tables — currency codes, ISO country codes, US state abbreviations — are reasonable natural primary keys. They are narrow, they read well in child tables, and they change on a decade timescale with a known process. Even there, teams that have lived through one code change tend to add a surrogate anyway. ## Costs of surrogates, honestly stated - An extra column and an extra index on every table. - An extra join to display the meaningful value. - If someone forgets the unique constraint on the natural key, duplicates creep in — the failure mode natural keys cannot have. This is the single most common real defect introduced by moving to surrogates. - Meaningless ids are harder to eyeball in logs and support tickets. ## Rule of thumb Make the primary key immutable, narrow, and meaningless; make the business identifier a unique constraint. When you find yourself wanting cascading updates on a primary key, treat that as evidence you chose the wrong key rather than as a feature to enable.