Rank the per-row write cost of a CHECK constraint, a UNIQUE constraint, and a FOREIGN KEY constraint on the same table, and explain what makes them differ by orders of magnitude.
answer
- Cost tracks reach: row → index → other table
- CHECK = CPU only, no pages, no locks
- UNIQUE = probe + leaf write + possible wait
- FK = parent probe + lock held to commit
- Exception: expensive predicate inside a CHECK
basics
~20 sCHECK 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.
solid answer
~60 sCheapest to most expensive: **NOT NULL ≈ CHECK << UNIQUE < FOREIGN KEY**. - **CHECK** evaluates a predicate over the row's own values, in memory, already in the CPU cache. No extra pages read, no extra pages written, no locks, no interaction with other transactions. Nanoseconds — unless the predicate is expensive in itself (a regex over a large text column, a user-defined function). - **UNIQUE** means an index. Per row: a descent and probe to prove no live duplicate, a leaf entry write, log records, occasional page splits — and a wait if another in-flight transaction inserted the same key. - **FOREIGN KEY** is the only one that reaches into another table: a probe of the parent's unique index, a visibility check, and a share-style lock on the parent row held until commit. That lock is what makes it a *concurrency* cost, not just an I/O cost, especially when many children point at one hot parent row. The underlying rule: cost scales with how far the check has to reach — same row, same table, another table, another transaction.
code
sql · 7 linesCREATE TABLE payment (
id bigint PRIMARY KEY,
reference text NOT NULL UNIQUE, -- index probe + leaf write per row
account_id bigint NOT NULL REFERENCES account(id), -- parent probe + parent row lock
amount numeric(12,2) NOT NULL,
CHECK (amount > 0) -- in-memory expression, no I/O, no locks
);go deeper
Give the ordering and the reason in one line: CHECK looks only at the row, UNIQUE needs an index, a foreign key needs another table.
Quantify the mechanisms — probe plus leaf write for UNIQUE, parent probe plus a lock held to commit for FK — and note NOT NULL is effectively free.
Emphasise that the FK cost is a concurrency cost and depends on fan-in to hot parents, and give the caveats where a cold unique index outweighs a cached FK.
Turn it into a budget: which invariants deserve index-backed enforcement, which are free to declare, and how constraint multiplicity shapes the write ceiling of a table.
## The organising principle: reach The cost ordering is not arbitrary. It follows how far outside the row being written the check must reach: | Constraint | Reaches | Extra I/O | Locks | Cross-transaction wait | |---|---|---|---|---| | NOT NULL | one column | none | none | no | | CHECK | the row itself | none | none | no | | UNIQUE | the same table's index | index probe + write | index-level | yes (colliding key) | | FOREIGN KEY | another table | parent index probe + row visit | parent row lock to commit | yes (parent locked) | Everything else follows from that table. ## NOT NULL and CHECK: essentially free A NOT NULL check is a bit test against the row's null bitmap — a handful of instructions. A CHECK constraint evaluates an expression over the row's own column values, which are already in memory because the engine just built the row. There is no page to fetch, no page to dirty beyond the row itself, no log record beyond the row's own, no lock, and no other transaction involved. Standard SQL deliberately restricts CHECK to values of the row being written — no subqueries against other tables — and that restriction is exactly what keeps it cheap. If a CHECK could query another table it would inherit all the costs of a foreign key plus a race condition (the referenced data could change afterwards, and the constraint would silently stop holding). The realistic exception: an expensive predicate. `CHECK (payload ~ '<some catastrophic regex>')` on a megabyte text column, or `CHECK (my_udf(col) > 0)` where the function does real work, can dominate the insert. The cost then belongs to the expression, not to the constraint mechanism. ## UNIQUE: an index on the write path A unique constraint is implemented as a unique index, so its cost is index maintenance plus a duplicate probe: - Descend the tree to the key position (3–4 page accesses, upper levels usually cached). - Probe for a live duplicate; under MVCC this may require judging whether existing entries point at visible rows. - Write the leaf entry, log it, occasionally split the page. - If another uncommitted transaction inserted the same key, wait for its outcome. Order of magnitude: microseconds when everything is cached, milliseconds when the index is larger than memory and the key is random. The write amplification also matters — an extra index means extra log volume, extra pages to flush, extra pages to replicate. ## FOREIGN KEY: another table plus a lock The FK check probes the parent table's unique index for the referenced key, usually visits the parent row to judge visibility, and then takes a share-style lock on it that survives until the child transaction commits. Three consequences: 1. **I/O against a table the statement never mentions.** If the parent is hot and small (a currency or status table), the probe is a buffer hit and the cost approaches that of a CHECK. If the parent is a huge table accessed randomly, it is a cache miss on the write path. 2. **Contention.** Lock entries accumulate on parent rows. Many children pointing at the same parent means every writer touches the same lock, and any transaction wanting that parent exclusively (deleting it, re-keying it) blocks the entire write stream. 3. **Deadlock surface.** Transactions that touch parents in different orders deadlock on locks their SQL never named. And the check runs in the other direction too: deleting or re-keying a parent must prove no child references it, which is a scan of the child table unless the child's FK columns are indexed. ## Caveats that make the ranking situational The ranking is about mechanisms; real numbers depend on data: - A unique index on a random UUID over a 500 GB table, cold cache, can easily cost more than a FK into a 20-row lookup table that never leaves memory. - A CHECK that calls a volatile function can beat both for sheer cost, though it will never cause contention. - Multiplicity dominates: six FKs and four unique indexes on one table means ten extra structures touched per inserted row, and that is usually the story behind "our inserts got slow as the schema grew". ## How to use this in design Treat CHECK and NOT NULL as free integrity — declare them liberally, they cost nothing at write time and give the optimizer usable facts (a validated CHECK can let a planner prune partitions or drop impossible predicates). Treat unique constraints as a deliberate index budget item: enforce each rule exactly once, keep the keys narrow. Treat foreign keys as a concurrency decision as much as a correctness one: keep them, index the child columns, and watch the fan-in to hot parent rows. In an interview, the sentence that lands is: "the cost tracks how far the check reaches — CHECK stays inside the row, UNIQUE touches an index in the same table, a FOREIGN KEY touches another table *and* takes a lock other transactions can queue on."
- Why does standard SQL forbid a CHECK constraint from querying another table?Because the guarantee could not be maintained. A CHECK is validated only when the constrained row is written, so a change in the other table afterwards would silently break it, and the engine would have to re-validate the whole table on every foreign change. Keeping CHECK row-local is what makes it both sound and nearly free.
- Is there a case where a CHECK constraint is genuinely expensive?Yes, when the predicate itself is expensive: a costly regular expression over a large text value, or a call to a user-defined function that does real work. The cost belongs to the expression rather than the constraint mechanism, but it is paid on every insert and on every update that touches the constrained columns.
saying these in an interview costs you the question
- Claiming CHECK constraints slow down inserts noticeably in the general case
- Saying all constraints cost roughly the same because 'the database checks them anyway'
- Missing that the foreign key's dominant cost is contention on parent rows, not I/O
- Assuming a unique constraint is cheaper than a unique index because 'it isn't an index'
- Recommending removing CHECK constraints for write performance