skip to content

What goes wrong when the value a table is keyed on changes, and how do you design for identifiers that might be corrected, re-issued, or restructured by an outside party?

level: middleimportance: should knowfreq 42%

answer

  1. Cascade stops at the database boundary
  2. Index entries are deleted and reinserted, not edited
  3. Warehouse sees a key change as a new entity
  4. Re-issued values silently repoint history
  5. Immutable id + mutable UNIQUE attribute + alias table

basics

~20 s

Changing a key value forces every referencing row to change too, churns every index containing it, and breaks references already stored outside the database - logs, caches, URLs, exports, partner systems. Design so identity never changes: a generated key inside, the business identifier as a mutable UNIQUE attribute, with history and aliases if values get re-issued.

solid answer

~60 s

Inside the database, a key change propagates. Every foreign key referencing it must be updated - either manually or by ON UPDATE CASCADE, which takes locks on and rewrites potentially large numbers of child rows in one transaction. Every index containing the key gets a delete plus insert per row, not an in-place edit. In a clustered-index engine the rows themselves move. Outside the database is worse, because you cannot cascade there: URLs already published, cached objects, logs and audit records, analytics warehouses, files exported to partners, and other services holding the old value. Those references become silently wrong rather than failing loudly. So the design is to make identity immutable and the business identifier an ordinary attribute: a generated primary key, the business value in a column with a UNIQUE constraint, and a history table if you need to know what it used to be. If identifiers are genuinely re-issued or restructured by an external owner, add an alias table mapping old values to the internal id and make lookups resolve through it, so old references keep resolving instead of breaking or, worse, matching the wrong row.

code

sql · 20 lines
sql
CREATE TABLE product (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku       text NOT NULL UNIQUE,
  name      text NOT NULL
);

CREATE TABLE product_sku_alias (
  sku         text PRIMARY KEY,
  product_id  bigint NOT NULL REFERENCES product(id),
  retired_at  timestamptz NOT NULL DEFAULT now()
);

-- lookup resolves current SKUs and retired ones alike
SELECT p.*
FROM   product p
WHERE  p.sku = :sku
UNION ALL
SELECT p.*
FROM   product_sku_alias a JOIN product p ON p.id = a.product_id
WHERE  a.sku = :sku;

go deeper

for a junior

Say that a changing key forces every referencing row to change and that generated keys avoid it; know that the business value should still be UNIQUE.

for a middle

Explain foreign key propagation and index churn concretely, and name at least two external places the old value survives.

for a senior

Add the warehouse and event-stream consequences, the re-issue hazard, and design the alias/history tables including what happens when an external owner reuses a value.

for a principal

Treat identifiers as a long-lived contract with external consumers; decide which identifier is publishable, who owns its lifecycle, and what the migration plan is if that owner restructures it.

## Why key mutation is expensive inside the database **Foreign key propagation.** Any child row referencing the old value must be updated in the same logical operation. With `ON UPDATE CASCADE` the engine does this for you, but it is not free: it takes row locks across every affected child table, produces write-ahead log volume proportional to the number of rows touched, and runs as one transaction, so a key change on a heavily referenced row can block writers for a long time. Without cascade you must sequence the updates yourself, and until you finish, the constraint either blocks you or you have had to defer or drop it - a window in which integrity is not enforced. **Index churn.** Updating an indexed column is not an in-place edit. The old index entry is removed and a new one inserted at a different position in the B-tree, which can split pages and fragment the index. Every index containing the key column pays this, and in engines where the table is physically clustered by the primary key, the row itself relocates, meaning every secondary index entry that points via the primary key is affected too. **Concurrency and correctness windows.** A key change is visible to other transactions at commit; anything that read the old value and is about to write using it now writes against a value that no longer exists. Long-running work that captured the old key must be re-checked. ## Why it is worse outside the database Cascade stops at the database boundary. The old value is already sitting in: - **URLs and bookmarks** shared with customers and indexed by search engines. - **Caches** keyed by the old value, which will keep serving until they expire. - **Logs, audit trails and event streams**, which are immutable by design and now reference something that does not exist. - **Analytics warehouses and data lakes**, loaded incrementally, where an updated key looks like a brand-new entity and the old rows become an orphaned entity that never appears again. - **Partner and third-party systems** that were given the identifier and have their own release cycle. None of these fail loudly. They quietly stop matching - or, catastrophically, start matching the wrong thing if the old value gets re-issued to a different entity. ## Re-issue: the failure mode people forget Mutability is bad; re-use is dangerous. Product SKUs get retired and reassigned; employee numbers get recycled; ISBNs and phone numbers get reassigned. If your key is the business value and the value is re-issued, historical rows silently attach to the new entity: last year's orders now appear to belong to a different product. That is a data-integrity failure that no constraint will catch, because at every moment the value is unique. ## The design that avoids all of it 1. **Generated, immutable primary key.** Identity is assigned by the system and never changes for the life of the row. Foreign keys point at that. 2. **Business identifier as an attribute** with a UNIQUE constraint so the rule is still enforced, and NOT NULL only if it is genuinely always known. 3. **History when the value matters over time.** A `customer_identifier_history` table recording (customer_id, value, valid_from, valid_to) lets you answer "what was their code in March" without touching identity. 4. **Alias/redirect table when old values must keep resolving.** Map every previously used value to the internal id, and resolve incoming lookups through it. This is how a URL containing an old slug or code can still land on the right record. Enforce that a given external value maps to exactly one internal id - and think explicitly about what happens if the external owner re-issues it, because that is the case where you must refuse the alias rather than silently repoint it. 5. **Never key on data owned by someone else.** If a third party can restructure their coding scheme, their code is an attribute, not your identity. ## When a natural key really is immutable Some are. ISO country codes and currency codes change rarely and with long notice; even then, codes have been retired and reused across decades, which is why long-lived systems still hit this. If you do key on such a value, the honest position is: you have accepted a small probability of an expensive migration in exchange for removing joins today. That is a legitimate trade - just make it consciously and write it down. ## What interviewers listen for Candidates who only say "use ON UPDATE CASCADE" have solved the smallest part of the problem. The strong answer names the outside-the-database references that cascade cannot reach, and the re-issue hazard, then proposes immutable internal identity with the business value modelled as a mutable attribute plus aliases.

  • Does ON UPDATE CASCADE make key changes safe?
    It makes them consistent within the database, but not cheap or complete. The cascade locks and rewrites every referencing row in one transaction, generating log volume and index churn proportional to the fan-out, which can stall writers on a heavily referenced row. And it reaches nothing outside the database - caches, published URLs, logs, warehouses and partner systems still hold the old value and will not be corrected.
  • An external partner re-issues a product code that your system already used for a retired product. What breaks?
    Any query or join based on the code now associates historical rows with the wrong product, and no constraint detects it because the code is unique at every instant. If the code is your primary key the damage is structural; if it is an attribute over an immutable internal id, the old rows still point at the old product and you simply refuse to alias the re-issued code to it, treating the new code as a new product.

saying these in an interview costs you the question

  • Believing ON UPDATE CASCADE fully solves key mutation
  • Assuming an update to an indexed column is edited in place rather than a delete plus insert
  • Ignoring references held outside the database - caches, URLs, logs, warehouses, partners
  • Not considering that an external identifier can be re-issued to a different entity
  • Keying on an identifier controlled by a third party who can restructure it

context

resolving links…