Why can a CHECK constraint not enforce a rule such as 'at most one active row per customer' or 'this value must exist in another table', and what mechanisms do relational engines offer instead?
answer
- CHECK sees one row, no subqueries
- Write skew: both pass alone, break together
- Partial unique index = 'at most one active'
- FK for cross-table, exclusion constraint for overlaps
- Trigger + SELECT COUNT is a race, not a rule
basics
~20 sA 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.
solid answer
~1 minA CHECK is a per-row predicate: the engine evaluates it against one candidate row version. Anything requiring another row is out of scope, and engines therefore reject subqueries inside CHECK expressions. The deeper reason is concurrency and revalidation. A cross-row predicate would have to be re-tested for every existing row whenever *any* row changes, and two concurrent transactions could each satisfy it in isolation while jointly breaking it — the classic write-skew shape. Enforcing that correctly requires either serialization or an index structure that makes the conflict physical. So use the mechanism built for each shape: - **'Value must exist elsewhere'** → a foreign key, which the engine backs with locking on the referenced row. - **'At most one active row per customer'** → a unique index over `(customer_id)` filtered to active rows, or over a computed expression. The index makes the conflict a physical collision, so it is safe under concurrency. - **'No two reservations overlap in time'** → an exclusion constraint where available. - **Genuinely procedural rules** → a trigger, plus explicit locking or a serializable isolation level, because a trigger alone re-creates the race it was meant to prevent. If a candidate answers 'write a trigger that does SELECT COUNT(*)', probe for the concurrency hole.
code
sql · 4 lines-- 'at most one active subscription per customer'
CREATE UNIQUE INDEX ux_subscription_one_active_per_customer
ON subscription (customer_id)
WHERE status = 'ACTIVE';go deeper
Know the boundary: a CHECK looks at one row only, so cross-table rules are foreign keys and cross-row uniqueness is a unique constraint.
Explain why the limit exists in terms of re-validation cost, and name partial unique indexes as the tool for conditional uniqueness.
Lead with the write-skew argument, then map three or four rule shapes to their proper mechanisms, and be explicit that a trigger needs explicit locking or serializable isolation to be correct.
Talk about reshaping the schema so invariants become per-row and index-enforced, and about the cost of invariants whose correctness depends on isolation-level discipline that reviewers must remember forever.
## The stated limit A CHECK constraint's expression may reference only the columns of the row currently being inserted or updated. It cannot reference other rows in the same table, cannot reference other tables, and mainstream engines disallow subqueries in the expression outright. Some engines will even reject references to non-deterministic functions for the same family of reasons. ## Why the limit exists It is tempting to see this as an implementation shortcut. It is not. Three problems make cross-row CHECKs genuinely hard. **Re-validation scope.** A per-row predicate has a beautiful property: writing row R can only affect R's validity. If the predicate could see other rows, then writing R could invalidate rows the statement never touched, so the engine would have to re-evaluate the constraint over some, possibly all, other rows on every write. Cost becomes unbounded and unpredictable. **Concurrency.** Suppose the rule is 'at most one active row per customer' and it is implemented as a predicate that counts sibling rows. Two transactions start simultaneously, each inserts an active row for customer 42, and each evaluates its count against a snapshot that does not include the other's uncommitted row. Both see zero existing active rows, both pass, both commit, and the invariant is broken while every individual check was satisfied. This is write skew, and it is the standard argument for why cross-row invariants need either serializable isolation or a physical enforcement structure. Under a snapshot-based MVCC engine at the usual READ COMMITTED or REPEATABLE READ levels, the naive check simply does not hold. **Deletion asymmetry.** Per-row constraints do not need to fire on DELETE. A cross-row constraint might — removing a row can break a rule such as 'each order has at least one line'. That drags in cascading evaluation and ordering questions that the simple model avoids entirely. ## The right tool per shape of rule **Referential rules — 'this value must exist in another table'.** Use a foreign key. It is not just declarative sugar: the engine takes a lock or equivalent protection on the referenced row so a concurrent delete of the parent cannot slip between your check and your commit. A CHECK with a subquery, even where an engine permitted it, would have no such protection. **'At most one X' / 'exactly one flagged row per group'.** Use a unique index over the grouping key, restricted to the qualifying rows — a partial or filtered unique index where the engine supports one, or a unique index on an expression that maps non-qualifying rows to NULL or to a distinct value. This works because uniqueness is enforced inside the index structure: two concurrent inserts of the same key physically collide there, one waits, and the loser fails. No isolation-level reasoning is required. This is the single most useful trick in this area and the answer interviewers are usually fishing for. **Range / overlap rules — 'no two bookings for the same room overlap'.** Some engines offer an exclusion constraint, which generalises uniqueness from equality to an arbitrary operator such as 'ranges overlap', backed by an index that can answer it. Where it is unavailable, the fallback is an explicit lock on the parent entity (the room row) plus a query, so that all writers for a room serialise on a single point. **Aggregate rules — 'total allocation per project must not exceed the budget'.** These are the hard ones. Options in rough order of preference: maintain the aggregate as a column on the parent row and put a plain CHECK on *that* row, updating it in the same transaction so the parent row's lock serialises writers; or use serializable isolation and accept retries; or enforce it asynchronously and reconcile. Trying to express it as a trigger with a SELECT SUM under READ COMMITTED is the classic broken answer. **Truly procedural rules.** A trigger is the general-purpose escape hatch and the last resort. It is imperative code with the engine's full visibility, but it inherits every concurrency problem above: the trigger body sees the same snapshot the transaction does, so it must take explicit locks, or run under serializable isolation with retry, to be correct. It is also invisible to the optimizer, harder to test, and easy to make slow. Reach for it only after the declarative options are exhausted. ## Materialising the rule so a CHECK can do the job A recurring senior move is to reshape the schema so a cross-row rule becomes a per-row one. Denormalising a running total onto the parent, or introducing a table whose primary key *is* the uniqueness rule (a `customer_active_subscription` table keyed by customer id), converts an invariant the engine cannot express into one it enforces for free. The cost is the extra write and the extra table; the benefit is that correctness stops depending on isolation levels and reviewer vigilance. ## What to say in an interview State the limit, give the concurrency reason rather than just 'the standard says so', then name the right mechanism for at least two shapes — foreign key for referential, partial unique index for 'at most one active' — and mention that triggers need explicit locking to be safe. That progression is what separates a memorised answer from an operational one.
- Someone implements 'at most one active row per customer' with a BEFORE INSERT trigger that runs SELECT COUNT(*) and raises an error if it is non-zero. What breaks?Under concurrency it does not hold. Two transactions insert for the same customer at the same time; each trigger reads a snapshot that excludes the other's uncommitted row, both see zero, both pass, both commit, and now two active rows exist. Fixing it requires serialising the writers — locking the customer row first, or running serializable isolation with retry — or replacing the trigger entirely with a partial unique index, which makes the collision physical.
- How would you use a unique index to express 'at most one active row per customer' when the table also holds many historical inactive rows?Create a unique index on customer_id restricted to the active rows — a partial index with a WHERE clause where the engine supports it. Historical rows are simply not in the index, so they never collide, while two concurrent attempts to make a second row active hit the same index key and one fails. Where partial indexes are unavailable, index a computed expression that yields customer_id for active rows and NULL for the rest, relying on the engine's treatment of NULLs in unique indexes.
saying these in an interview costs you the question
- Proposing a subquery inside a CHECK constraint
- Claiming a trigger that counts sibling rows is equivalent to a constraint
- Assuming the default isolation level prevents write skew
- Reaching for application-level locking to enforce a database invariant
- Not distinguishing 'the engine forbids it' from 'it would be incorrect under concurrency'