Keys & Integrity Constraints

CHECK is cheapest: pure CPU on the row's own column values, no extra pages, no locks. UNIQUE costs an index probe plus an index write. FOREIGN KEY costs a probe into another table plus a lock on the parent row held to commit, so it is the only one that makes writers contend.

Both write a leaf entry. The unique one must first read the key's position and prove no live duplicate exists, and if another uncommitted transaction inserted the same key it must wait for that transaction to finish. That read and that wait cannot be deferred.

A CHECK sees only the single row being written, so it cannot inspect other rows or other tables, and mainstream engines forbid subqueries in it. Cross-row rules need a different mechanism: a unique or partial unique index, a foreign key, an exclusion constraint, or a trigger.

Catch the violation when a duplicate is genuinely an error the user should hear about; use an insert-if-absent statement when a duplicate is a normal outcome. Match on the constraint name, not the message text, and remember an error may abort the transaction unless you wrap the insert in a savepoint.

Violations now surface at COMMIT with a constraint name but no guilty statement, and the entire batch is lost. The engine queues one pending check per affected row, costing memory. Concurrent sessions see nothing invalid, but hold-open locks last longer.

Stop the app writing NULLs, backfill existing NULLs in small batches, add CHECK (col IS NOT NULL) NOT VALID (brief lock), validate it under a weak lock, then SET NOT NULL — which PostgreSQL 12+ can do without rescanning because the validated CHECK proves it.

The constraint is enforced for new writes but marked untrusted, so the optimizer will not reason from it — losing simplifications like join elimination and predicate-based partition elimination. Fix by cleaning the data, then ALTER TABLE ... WITH CHECK CHECK CONSTRAINT to rescan and re-trust it.

It takes a shared, key-preserving lock on the parent row — enough to stop the parent being deleted or its key changed before commit, but weak enough that many children can be inserted concurrently. An exclusive lock would serialise every child insert under the same parent.

The referenced columns must be provably unique for every row of the table. The standard requires a declared PRIMARY KEY or UNIQUE constraint; engines that accept a bare unique index still require it to be full-table, non-partial, over plain columns and immediately checked. Partial and expression unique indexes never qualify.

Plain UNIQUE (email) is wrong — it blocks re-registering a deleted address. Enforce it with a partial (filtered) unique index on email restricted to rows where deleted_at IS NULL. Where partial indexes are unavailable, index a generated column that holds the email only for live rows.

Three routes: declare the key with NULLS NOT DISTINCT where the engine supports it (SQL:2023, PostgreSQL 15+); replace NULL with a non-null sentinel and use an ordinary unique constraint; or add a partial unique index over the rows where the column IS NULL. The sentinel is the portable choice.

Cascading referential actions multiply: one parent row deletes all its children, each child deletes its own children, and every deleted row costs table and index maintenance, logging and a lock held to commit. Bound it by deleting in batches yourself, archiving instead of deleting, or dropping whole partitions.

Put stable, structural, row- or table-scoped invariants in the schema; put volatile or contextual policy in code. Rules the engine cannot express — cross-service, aggregate, temporal — need an explicit mechanism such as index-based enforcement, serialisable transactions, or asynchronous reconciliation with alerting.

Settle the semantics, enforce in the application first, dedupe the history in batches, build the unique index without blocking writes (CREATE UNIQUE INDEX CONCURRENTLY / ONLINE=ON), then attach it as a constraint with a brief lock. Uniqueness has no unvalidated mode, so the index build is the staging mechanism.

The schema claims an invariant the data does not have: bad rows accumulate silently, and code that trusts the constraint breaks later. Verify empirically — attempt an insert that must fail. If it succeeds, the constraint is decoration, and you need application checks plus a data audit.

No. Keep constraints NOT DEFERRABLE by default and make individual ones deferrable where a real cycle or swap demands it. Blanket deferrability costs error attribution and pending-check memory, restricts features like upsert conflict inference, and hides modelling problems.

Default to named constraints: they are the objects foreign keys, upsert targets and schema tooling can cite, and they are hard to delete by accident. Allow bare unique indexes only where constraint syntax cannot express the rule — conditional or computed uniqueness — and document each exception.

Do not let correctness depend on engine NULL rules, which disagree. Either move the optional identifier into its own table where the column is NOT NULL and uniquely constrained, or keep it in place with an engine-specific object (filtered index on SQL Server, plain unique constraint elsewhere) chosen deliberately.