Unique Index Constraint¶
Software or tool — instantiates Collision-Free Mapping Design
A database or datastore rule preventing duplicate target values inside a scoped collection.
A unique index constraint is the storage layer's own refusal to hold two rows carrying the same target value within a declared scope — enforced at write time, atomically, by the datastore engine rather than by application code that can be raced. Its whole reason to exist is that an application's own "check whether this value is taken, then insert it" is not safe under concurrency: between the check and the insert, another request can slip the same value in. The unique index moves the guarantee to the one place that can hold it — the commit itself, under the engine's own locking — so that a second distinct source is rejected, not silently accepted. It is the archetype's practical injectivity guard rendered as a declarative rule you attach to a collection and forget.
Example¶
A SaaS product treats a user's email address as their login identity. Two people (or one person clicking "Sign up" twice on a flaky connection) submit the same address within the same instant. Each request runs the app's tidy SELECT ... WHERE email = ? check, finds no existing row, and proceeds to INSERT — both checks passed, because both ran before either insert landed. Without a guard, the table now holds two accounts that answer to one login, and the next password reset is a coin flip over whose account it repairs.
With a unique index declared on lower(email), the story ends differently: the first INSERT commits, the second hits the index and comes back as a duplicate-key error the moment it tries to commit. The application catches that error and shows "an account with this email already exists." The race still happened — but the collision was refused at the only line where refusing it is reliable.
How it works¶
- Declare it over the identity scope, not a display field. The index is placed on the column(s) that are the target value — often normalized, e.g.
lower(trim(email))— so that "Alice@x.com" and "alice@x.com " cannot both be accepted. - Compose it to set the partition. A composite index on
(tenant_id, email)makes the value unique per tenant, not globally; a partial index (WHERE status = 'active') makes only active values unique, exempting retired rows. - Let the engine enforce atomically. The uniqueness check and the write are one indivisible operation under the storage engine's locking; there is no window for a concurrent twin to slip through.
- Surface the violation as a catchable error. A collision becomes a duplicate-key exception at commit that the application routes — to a friendly message, a retry, or the quarantine queue.
Tuning parameters¶
- Scope columns — global versus per-partition (
tenant_id,region,day). Wider scope forbids more; narrower scope permits the same value across partitions but demands the partition key be part of every lookup. - Partial predicate — which rows must be unique (e.g. only
active). Exempting retired rows enables safe reuse but widens the window for stale-value confusion. - Normalization / collation — case- and accent-folding before indexing. Too aggressive fuses genuinely distinct values ("O'Brien"/"OBrien"); too loose lets casing variants collide-by-omission.
- Deferrable vs. immediate — whether the check fires per-statement or at transaction end. Deferring allows transient duplicates mid-transaction (needed for swaps and renumbering).
- Null handling — whether multiple NULLs count as distinct. Governs whether "value not yet assigned" rows can coexist.
When it helps, and when it misleads¶
Its strength is that it is the only guard that survives concurrency, because it is enforced by the storage engine's own locking rather than by a check the application runs and then acts on a moment later — that check-then-insert gap is a textbook time-of-check-to-time-of-use race.[n1] Declared once, it holds forever, for every code path, including the ones the author forgot.
Its central limitation is that it only guards what lives inside one collection. Shard the table across nodes, or split identity across two datastores, and no single unique index spans them — uniqueness must then be enforced upstream by a controlled allocator or by folding the shard key into the value. Its classic misuse is over-tight normalization: fold too much and the index rejects legitimately distinct sources, so users who differ only in a character the collation erased are told they already exist. The guarding discipline is to normalize deliberately and test the collation against real edge cases, and to place the index on the true identity scope rather than a mutable display attribute.
How it implements the components¶
unique_target_constraint— it is the constraint: a second distinct source offering an already-held target value is refused at commit.namespace_partition_rule— composite and partial index definitions encode the scope (per-tenant, active-only) within which uniqueness must hold.collision_detection_guard— the index detects the collision synchronously, at the instant of write, raising a duplicate-key error rather than letting the twin land.
It does not define what counts as a distinct source, scan legacy data for collisions that predate it, or preserve source-to-target evidence — those are Duplicate Target Scan and Preimage Audit Log. Nor does it hold value lifecycle states (Namespace Reservation Table) or adjudicate merges (Collision Quarantine Queue).
Related¶
- Instantiates: Collision-Free Mapping Design — it is the archetype's synchronous, at-commit uniqueness guard.
- Sibling mechanisms: Deterministic ID Allocator · Booking Lock · Duplicate Target Scan · Namespace Reservation Table · Hash Collision Check · Collision Quarantine Queue · Preimage Audit Log
Editorial Notes¶
Form Classification¶
Form family: Control, Automation & Runtime
Rationale: Unique Index Constraint is defined in the frozen evidence as: A database or datastore rule preventing duplicate target values inside a scoped collection. Its operative deployed or enacted form is therefore Control, Automation & Runtime.
Nearest alternative: Rule, Policy & Commitment — Rule, Policy & Commitment can support this mechanism, but the evidence centers the concrete operation described above rather than the alternative family's defining operation.
Review outcome: Adjudicated after independent review; medium confidence.
Origin Attribution¶
Primary origin: Computer Science & Software Engineering
Origin pattern: Single lineage
Present-day reach: Specialized
Rationale: PostgreSQL Documentation: Constraints documents that database systems enforce uniqueness with unique constraints and indexes, including multicolumn uniqueness. This is direct, mechanism-specific evidence for computer science as the best-evidenced historical home of the operation—A database or datastore rule preventing duplicate target values inside a scoped collection.—rather than evidence merely that the operation is useful there. The retained alternates record genuine adjacent lineages; later portability is represented separately by domain_reach=specialized.
Related originating lineages:
- Engineering & Design — Engineering design, reliability, and systems-safety practice supplies a parallel or contributing lineage for the mechanism's defining operation: a database or datastore rule preventing duplicate target values inside a scoped collection.
- Organizational & Management Science — Organizational Management supplies a historically relevant adjacent lineage or formative practice for the operation—A database or datastore rule preventing duplicate target values inside a scoped collection.—but the adjudicated evidence more directly locates the defining lineage in computer science.
- Systems Thinking & Cybernetics — Systems science's feedback, boundaries, control, and regulation tradition contributes a separate formative lineage to the mechanism's unique index constraint logic.
Review resolution: The blind reviewers disagree on primary lineage (organizational_management versus computer_science). The defining operation is: A database or datastore rule preventing duplicate target values inside a scoped collection. The researched PostgreSQL Documentation: Constraints establishes that database systems enforce uniqueness with unique constraints and indexes, including multicolumn uniqueness. That source therefore supports computer science as the historical origin. organizational management remains in the uncapped alternates where it contributes a formative practice, but application or governance is not itself proof of origin. origin_mode=single_lineage records lineage construction; domain_reach=specialized separately records later applicability.
Encyclopedia synthesis: The exact catalogued form synthesizes established practice rather than reproducing a single standard historical label.
Review outcome: Researched adjudication after independent review; high confidence.
Sources consulted:
Notes¶
[n1] A time-of-check-to-time-of-use (TOCTOU) race is the gap between checking a condition and acting on it, during which another actor can invalidate the check. It is the standard argument for enforcing uniqueness inside the storage engine rather than in application code. ↩