You are setting the schema convention for a large database: should uniqueness always be declared as a named table constraint, or are bare unique indexes acceptable? How would you decide, and what follows from the choice?

## Framing the decision The two forms enforce identical rules at identical cost — the engine backs a unique constraint with a unique index. So this is not a performance decision. It is a decision about **which objects the rest of the system is allowed to depend on**, and about failure modes over a schema's lifetime. ## The case for constraints as the default **Referenceability.** Foreign keys resolve against declared keys; strict engines accept nothing else, and even the lenient ones exclude partial, expression, invalid and deferrable indexes. If a table's business identifier is expressed only as a bare index, a future foreign key is blocked until someone reworks the schema. **Tooling.** Migration frameworks, schema-diff tools, ORM reverse-engineering and documentation generators model constraints as part of the logical schema and indexes as physical tuning. A rule expressed as an index tends to be dropped from generated models and from the mental model of anyone reading them. **Accident resistance.** Index-maintenance automation — "drop indexes with zero scans in 90 days" — is common and correct in spirit. A bare unique index that is never used for reads is exactly what such a script targets, and dropping it silently removes a data-integrity rule. A constraint cannot be removed that way. **Naming and error handling.** Duplicate-key errors carry the object name. A convention like `<table>_<columns>_key` lets a single mapping layer convert violations into specific API errors instead of a generic 500. This works for indexes too, but only if the same naming discipline is applied. **Readability of intent.** `UNIQUE (tenant_id, code)` in the table definition states a fact about the domain. `CREATE UNIQUE INDEX ...` three files later reads as tuning, and reviewers treat it accordingly. ## When a bare index is the right call Constraint syntax accepts a column list and nothing else. Reach for an index when the rule needs more: - **Conditional uniqueness** — unique among live rows, among rows in a given state, among non-archived tenants. - **Computed uniqueness** — unique on a normalized or derived value. - **Collation/opclass or ordering specifics**, or **included payload columns** for index-only reads that a constraint's index cannot express. The honest phrasing of the convention is therefore *capability-driven exception*, not "use whichever you like". A reviewer should be able to ask "which constraint capability is missing here?" and get a one-line answer. Sometimes the better move is to change the model instead of taking the exception: moving archived rows to a history table restores a plain constraint; materialising a normalized value as a generated column turns computed uniqueness back into an ordinary constraint. Both trade a little storage or migration work for a schema that stays inside the simple rules. ## Lifecycle consequences to plan for **Rebuilds.** A constraint owns its index. You cannot drop that index alone, so replacing a bloated one means either an engine feature that lets an existing unique index be adopted as the constraint's index, or a drop-and-recreate of the constraint — which is heavier and, done carelessly, leaves a window in which the rule is not enforced. Bare indexes can be built alongside and swapped freely. If you run large tables with regular index maintenance, this is a genuine argument in the other direction, and worth writing into the runbook rather than discovering during an incident. **Enforcement gaps.** Any scheme where the rule is temporarily absent must be treated as a correctness event, not a maintenance detail: duplicates inserted during the gap will block the rebuild at the end and require data surgery. **Surrogate keys.** If exceptions exist, they imply the affected tables carry a surrogate primary key so children have something stable to reference. Make that part of the convention rather than a case-by-case discovery. **One rule, one object.** Forbid declaring both a constraint and an equivalent index on the same columns. It is duplicated write cost for no gain and it appears surprisingly often after two migrations from different authors. ## Making the convention stick A convention only works if it is checkable. Practical enforcement: a naming standard for both forms; a periodic catalog query that flags redundant index/constraint pairs and unique indexes with no accompanying comment; a review checklist item asking which capability justified each bare unique index; and a rule that index-cleanup automation ignores unique indexes entirely. The convention should also state the consequence chain explicitly — exception implies no FK target implies surrogate key — because that is the part teams rediscover painfully six months later when someone needs to reference the table.