skip to content

How do you decide whether a business invariant should be enforced by a database constraint or by application code, and what do you do about invariants that no constraint can express?

level: seniorimportance: should knowfreq 48%

answer

  1. Universal, no race window, permanent
  2. Single-row → CHECK; referential → FK; unique → UNIQUE
  3. Multi-row → materialize into one row + CHECK
  4. Or lock the parent / SERIALIZABLE + retry
  5. App-only = policy, external data, advisory

basics

~20 s

Push every invariant the engine can express into the schema — it then holds for all writers with no race window. Invariants spanning many rows or tables need a serialized write path: a lock on a designated row, a serializable transaction, or a derived column kept correct by a trigger.

solid answer

~60 s

Default to the engine. A declared constraint is enforced for every writer — your service, another service, a batch job, a manual UPDATE — with no gap between check and write, and it cannot be forgotten by a new code path. Anything single-row or referential (NOT NULL, CHECK, UNIQUE, FOREIGN KEY) belongs there without debate. The hard cases are **multi-row invariants**: 'no overlapping bookings for a room', 'account balance never negative across concurrent withdrawals', 'exactly one primary contact per customer', 'total of order lines equals the order header total'. A plain CHECK cannot see other rows, so you need one of: a constraint type that does cover ranges (exclusion constraints where the engine offers them), a **materialized guard** — a derived column or summary row that turns the multi-row rule back into a single-row CHECK — kept correct by a trigger, a **serialization point** such as locking the parent row (`SELECT ... FOR UPDATE`) so conflicting writers queue, or true SERIALIZABLE isolation with retry. Application-only enforcement is acceptable for rules that are policy rather than integrity, change frequently, or need context the database does not have — but be honest that it is advisory.

code

sql · 6 lines
sql
ALTER TABLE accounts
  ADD CONSTRAINT ck_accounts_balance_non_negative CHECK (balance >= 0);

-- withdrawal, in one transaction
UPDATE accounts SET balance = balance - 50 WHERE id = 42;   -- fails if it would go negative
INSERT INTO ledger_entries (account_id, amount) VALUES (42, -50);

go deeper

for a junior

Know the declarative constraint types and that constraints are safer than checks in code because they apply to all writers.

for a middle

Classify invariants and name the mechanism for each class, including why a CHECK cannot see other rows.

for a senior

Argue the tradeoffs concretely — materialized aggregate plus CHECK versus explicit locking versus serializable with retry — and state the contention each buys.

for a principal

Set the org-level rule about which invariants are schema-owned versus service-owned, how policy rules stay out of migrations, and what reconciliation exists for invariants deliberately left unenforced.

## Why the default is the engine A constraint has three properties application code cannot match: 1. **Universality.** It applies to every connection: your service, the reporting job, the data-fix script, the next team's service. Application checks are enforced only on the paths that remember to call them. 2. **No race window.** Constraint evaluation happens inside the write, under the engine's own concurrency control. Check-then-act in application code always has a gap. 3. **Permanence.** Constraints survive refactors, rewrites, and language changes; they are documentation that cannot go stale, because a stale constraint fails loudly. The costs are real but usually smaller than people claim: constraints add write-time work (a foreign key requires a lookup on the referenced key, a unique constraint requires index maintenance), they must be satisfied by every fixture and test, and changing them requires a migration. ## The taxonomy of invariants **Single-row, declarable.** `amount > 0`, `status IN (...)`, NOT NULL, a value matching a format. Use CHECK / NOT NULL. No argument. **Referential.** A child must point at an existing parent. Use FOREIGN KEY with an explicit ON DELETE action, chosen deliberately (RESTRICT for 'this should never happen silently', CASCADE for genuinely owned children, SET NULL for optional links). **Uniqueness / scoped uniqueness.** UNIQUE, possibly composite (per tenant) or partial (live rows only). **Multi-row within a table.** 'No two reservations for the same room overlap in time', 'exactly one row per customer flagged primary'. Some engines offer range **exclusion constraints** that express overlap directly; where they do not, the standard tricks are a partial unique index (unique on `(customer_id)` filtered to `is_primary`, which enforces at-most-one), or a serialization point. **Cross-table / aggregate.** 'Sum of order lines = header total', 'balance never negative', 'a project can have at most 10 active members'. No declarative form exists in mainstream SQL. Options: - **Materialize the aggregate.** Keep `orders.total` or `accounts.balance` as a real column with `CHECK (balance >= 0)`, and update it in the same transaction as the detail rows. The multi-row rule becomes a single-row CHECK the engine can enforce. Writers naturally serialize on that row, which is both the mechanism and the throughput limit. - **Trigger.** A trigger recomputes or validates on write. It keeps enforcement in the engine (universal), at the cost of logic hidden from application developers and harder debugging; triggers also need care to be correct under concurrency — a trigger that runs a SELECT to validate has the same phantom problem as application code unless writers are serialized. - **Explicit locking.** `SELECT ... FOR UPDATE` on the parent row before validating and writing children. Conflicting transactions queue; correctness is restored at the price of contention on hot parents. - **SERIALIZABLE isolation.** Correct by construction for these read-then-write invariants, but requires retry-on-serialization-failure everywhere and costs throughput under contention. ## When application enforcement is the right call - The rule is **policy that changes often** — trial limits, feature entitlements, pricing eligibility. A migration per policy change is a bad trade. - The rule needs **data the database does not hold** — an external service's answer, the caller's identity, time-zone-aware business hours. - The rule is **advisory or soft** — warnings, nudges, workflow gates. - Legacy data cannot satisfy it and cleanup is not on the table; then be explicit that the invariant is aspirational rather than pretending a constraint exists. ## Anti-patterns to name - 'The ORM handles it' — the ORM only handles writes that go through the ORM. - 'We removed foreign keys for performance' with no measurement, then discovering orphan rows a year later during a migration. - Validating in application code *and* claiming the invariant is guaranteed, when other writers and admin scripts bypass it. - Triggers holding critical business logic that no one on the team knows exists. ## How to answer State the default (engine), classify the invariant (single-row / referential / unique / multi-row / cross-table), name the concrete mechanism for the class, and be explicit about what you give up: contention on the serialization point, retries under serializable, or an honest 'this one is advisory'.

  • A team wants to drop foreign keys 'for write performance'. How do you evaluate that?
    Ask for the measurement: a foreign key costs an index lookup on the referenced key per write, which is rarely the bottleneck unless the parent index is hot or missing. Weigh it against the cost of orphan rows — which surface as silent wrong results and painful migrations later. If they are genuinely needed off, that is a deliberate decision with a documented owner for the invariant and a periodic reconciliation job, not a silent removal.
  • You enforce 'balance never negative' with a CHECK on a materialized balance column. What is the concurrency consequence?
    Every writer to that account must update the same row, so concurrent withdrawals on one account serialize behind a row lock. That is exactly what makes the invariant safe, but it caps per-account throughput. If a single account is hot, you shard the balance into sub-rows and reconcile, or accept the queueing — which is usually the right answer because the invariant is a money invariant.
  • Where do triggers fit?
    A trigger keeps enforcement inside the engine, so it applies to every writer — the main advantage over service code. The cost is invisibility: logic that fires without appearing in any application call path is hard to debug and easy to forget during a rewrite. Use them for narrow integrity work such as maintaining a derived column, not for broad business rules.

saying these in an interview costs you the question

  • Saying 'the ORM validates it' as if that covered every writer
  • Removing foreign keys for performance without measurement or a replacement owner for the invariant
  • Believing a CHECK constraint can reference other rows or tables in mainstream SQL
  • Enforcing a cross-row rule with a plain SELECT-then-write in application code and calling it guaranteed
  • Pushing every business policy into constraints, so routine policy changes require schema migrations

context

resolving links…