What doesn't work: fabricating a placeholder identifier to satisfy a NOT NULL constraint

When a unique, non-null identifier column is required but the real identifier isn't known yet at write time, fabricating a synthetic value to satisfy the constraint — instead of making the column nullable or resolving identity before writing — creates a fake record that is indistinguishable from a real one everywhere downstream.

The signal that gives it away

- Two or more independent write paths create the same kind of entity, and at least one of them doesn't have the "real" identifier available at write time. - The identifier column holds values that follow an obvious synthetic pattern — a fixed prefix plus a random suffix (a per-run UUID fragment, a short random hex string) — rather than the shape of the real-world identifier (a tax ID, an external system's ID, a checksum-validated code). - More than one placeholder format exists at once (e.g. one writer prefixes with one tag, another writer with a different tag and a different suffix length). This is the tell that no single place owns "what to do when identity is unknown" — every writer reinvented its own answer. - The validator/value-object that is supposed to guard the "real" identifier format accepts the fabricated values as valid — there is no way, anywhere in the code, to ask "is this a real identifier or a placeholder?" without hardcoding the fabrication pattern. - A read path that deduplicates or joins on that identifier shows the same real-world entity twice — once under the placeholder, once under the real value — with the record's fields split across the two rows (part of the data on one row, part on the other, neither complete). - The count of such split-in-two rows is large and grows monotonically over time (tens of thousands after months, in one observed case), because nothing ever merges them back together.

How it's exploited / how to diagnose it

Concretely, in one observed case on a production system: an entity cache table held ~125k rows, of which ~68k carried a fabricated identifier in the identifier column, in two different fabricated formats produced by two different writers. Of those, ~24k had a "twin" — a row created with the genuine identifier and a separate row created earlier with a placeholder for the same real-world entity, each holding half the record's enrichment. The diagnostic path that surfaced it, reusable on any system with the same smell:

1. Enumerate every write path that can create a row of that entity type (six were found for a single table in one observed case), and tabulate for each: what it deduplicates on before inserting, whether it fabricates an identifier and in what format when the real one is unknown, and which of the *other* writers' matching keys it actually populates. In one observed case, one writer deduplicated only on a loosely-matched free-text field and never wrote the normalized secondary key (e.g. a canonical domain/URL) that a second writer relied on for its own dedup — so anything the first writer created was invisible to the second writer's matching logic and got re-inserted as new. 2. Check whether the value-object/validator for the real identifier format accepts the placeholder format. If it does, that's the actual mechanism of the failure — not a symptom of it. A fabricated value that a formally-typed "verified identifier" happily accepts is now, for every consumer of that type, exactly as trustworthy as a real one. 3. Check whether any writer promotes a placeholder to a real identifier once the real one becomes known later. In one observed case only one of six writers did this, and only on an exact secondary-key match (not a fuzzy one) — so the overwhelming majority of fabricated rows never self-heal even after the real data arrives elsewhere. 4. Before touching any row, trace every foreign key that points at the entity table and its delete behavior. ON DELETE CASCADE on a child table means a "just delete the obviously-fake rows" cleanup silently deletes real child records that happen to be attached to the placeholder side of a split entity (in one observed case, thousands of person records with paid-for enrichment data were attached to placeholder company rows via a cascading FK). Also look for reference columns with no enforced foreign key at all (e.g. an array-of-IDs column) — these are invisible to a schema-level FK audit, won't be caught by ON DELETE CASCADE/SET NULL semantics, and go silently stale after any merge or cleanup. See the general tradeoff between CASCADE and RESTRICT here: [ON DELETE CASCADE tutorial (DataCamp)](https://www.datacamp.com/tutorial/sql-on-delete-cascade). 5. Search prior decision logs / commit history for the same symptom noted before. A fabricated-identifier collision mentioned once and patched only in the one query that hit it (e.g. "exclude placeholder-format values from this particular dedup check") is a strong sign the defect has already been rediscovered and locally rattled down multiple times without anyone escalating it to the write side. In one observed case, the identical signal had been logged five separate times across several months — each time patched in the one place it was noticed, never traced back to "writers are allowed to fabricate identity." 6. Once mapped, merging the split pairs is data surgery, not a code fix: match real/placeholder pairs on a normalized secondary key (name + domain, or equivalent), reassign every foreign key from the placeholder row to the real row *before* deleting the placeholder, re-run any denormalized fields that cached the wrong row's data, and only then delete — inside one transaction per pair, with before/after row counts logged. This is inherently the kind of merge that entity-resolution / record-linkage literature treats as its core problem: matching records across data quality gaps ([Record linkage](https://en.wikipedia.org/wiki/Record_linkage) — deduplication is explicitly one of its standard names, and the field's own literature notes linkage quality degrades sharply as identifier quality drops, which is exactly the failure mode here).

What doesn't work

- Excluding the placeholder format from one query's dedup or matching logic. This fixes exactly the one read path that was noticed broken; every other writer and reader in the system still treats the fabricated value as a legitimate identifier. In one observed case this exact patch was applied — and quietly bypassed by new writers — five times. - Trusting the "looks unique" property of the placeholder as if it were a stable key. A prefix-plus-random-suffix value is unique *per row*, but not stable across writers or across separate runs of the same writer — two different runs fabricate two different values for the same real-world entity. Uniqueness satisfies the constraint; it buys nothing for deduplication. - Deleting rows that "look like placeholders" as a cleanup pass without checking delete semantics first. Any ON DELETE CASCADE foreign key pointing at that table turns a "remove the obviously-fake rows" cleanup into a silent loss of legitimate child data that happened to be attached to the placeholder side of a split entity — contacts, events, associations, whatever hangs off that table. There is no warning at delete time; the loss is discovered later, if at all. - Fixing only the write path where the problem was first noticed (say, the initial-discovery writer) while leaving other writers free to fabricate their own placeholder format. As long as more than one code path can independently decide "I don't know the identifier, I'll invent one," new duplicates keep forming even after the historical backlog has been merged away. - A generic "satisfies NOT NULL" value-object or validator that doesn't distinguish real from fabricated identifiers. If the type that's supposed to represent "a verified identifier" accepts the placeholder shape too, no downstream code — including code written long after the placeholder mechanism is forgotten — can ever tell the two apart by inspecting the value alone. - Merging duplicate rows before every foreign key pointing at the table has been accounted for, including unenforced reference columns (arrays of IDs with no FK constraint) that don't show up in a schema-level foreign-key audit and will silently go stale — pointing at a row that's about to be deleted — after the merge. - Treating the fix as done once the historical duplicates are merged, without a single shared identity-resolution entry point that every writer is required to call. The backlog is a one-time cost; the fabrication mechanism, if left in even one writer, keeps generating new duplicates going forward. The only fix root enough to stick is: never allow more than one writer to decide "this identifier is unknown" independently — resolve identity in one place, and make the column nullable there so "unknown" is representable honestly (a NOT NULL constraint that cannot yet be satisfied truthfully means the schema is asking for a nullable column, not a fake value — see the standard distinction between "no value" and any in-domain value, including a look-alike one, in [Null (SQL)](https://en.wikipedia.org/wiki/Null_(SQL))).

Related

- Fetch coalescing + in-flight dedup + warm cache: turning a 37-fetch waterfall into a 684ms page — the same root shape (independent callers/writers unaware of each other duplicating work) in a caching context instead of an identity context. - Soft vs. hard cascade invalidation: when 'confirm, then delete downstream' destroys data you paid for — a different case where a cascading operation is destructive specifically because of what it touches downstream; same discipline of tracing cascade effects before acting. - What doesn't work — stacking a custom retry layer over a library that already retries — another "what doesn't work" where the fix was layered on top of the symptom instead of removing the root double-mechanism underneath.