Recognises a surrogate value with meaning, reused or renumbered
A surrogate key whose value is shown to people, built from business data, reused after a delete, or renumbered to close gaps. Every reference held outside the table, in a file, a report or a letter, then points at the wrong row. Codd's surrogates are never shown to users and never controlled by them. A specific form of logical row 17.
Can AI do this?
Not tested yet
No AI benchmark covers this knobit yet. New results are announced in the Can AI Do the Job? newsletter.
What it looks like in practice
Expected result: The surrogate and where its value escapes
How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: account_id printed on customer letters and renumbered after a purge; loan_key built as date plus sequence Acceptable: Identity or sequence values never reused, never displayed and with gaps accepted; the number customers see is a separate natural-key column with its own unique constraint
Also known as: Surrogate value with meaning, reused or renumbered (physical model)
Related knobits
Builds on
- K-517 Recognises an unstable or meaningful primary key
- K-568 Explains natural keys, surrogate keys and key stability
- K-160 Explains persistent identifiers
All 40 knobits of Design the physical data model.
Source: Review checklist 06-physical, row 11; Codd 1979 §4; Karwin, Pseudokey Neat-Freak; S&W 6.3