Databases
Database constraints: interview questions and how to answer them
A constraint is an invariant the database enforces on every write, from every client, including the ones that bypass your application.
Written and reviewed by Sahil Srivastav
What it actually is
A constraint is a rule the storage engine checks on every write: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY, and exclusion constraints. Unlike validation in application code, it holds for every writer — your service, a second service, a migration, a backfill script, someone in psql at 2am fixing an incident.
The property that makes constraints different in kind rather than degree is that they are enforced atomically with the write. Application validation is a separate step with a gap before the write, and under concurrency that gap is where the bug lives. Two requests both check that an email is unused, both find nothing, both insert. The check provided no protection whatsoever; only a unique index would have.
So the accurate framing is that application validation exists for the user experience — a clear message, fast feedback, no round trip — and constraints exist for correctness. They are not alternatives and you generally want both, serving different purposes.
Why it matters in production
Because every other safeguard can be bypassed, and the ones that get bypassed are the ones nobody remembers. The second service written a year later does not know about your validation layer. The data-fix script run during an incident does not run your validators. The ORM save path someone added skips the hook. The constraint is the only rule that holds for all of them, permanently, without anyone having to know it exists.
And because the failures it prevents are the expensive ones: duplicate charges, orphaned child rows pointing at deleted parents, negative balances, two bookings for one seat. These are not degradations — they are states the business logic assumes cannot happen, so downstream code has no handling for them and behaves arbitrarily when they occur.
How it works
UNIQUE is an index, and that is why it works
A unique constraint is implemented as a unique index, and the enforcement happens at write time under the index's own locking. That is precisely why it closes the check-then-insert race: the second concurrent insert fails at the index rather than passing an earlier check. It also means uniqueness gives you a usable index for free.
FOREIGN KEY, and the lock it takes
A foreign key guarantees the referenced row exists, preventing orphans. The non-obvious consequence is that inserting a child row takes a lock on the parent row to stop it disappearing mid-transaction — which is a real source of contention on a hot parent, and of deadlocks when two transactions touch parents in different orders.
CHECK for row-local invariants
A CHECK can express anything computable from the row: amount > 0, ends_at > starts_at, a status in an allowed set. It cannot reference other rows or other tables, which is the boundary people hit — cross-row invariants need an exclusion constraint, a trigger, or a different schema shape.
Partial and exclusion constraints for conditional rules
A partial unique index enforces uniqueness only over a subset — one active subscription per user via UNIQUE (user_id) WHERE status = 'ACTIVE' — which expresses rules application code usually implements as a race-prone check. An exclusion constraint generalises this to non-equality: with a range type, it can guarantee no two bookings for the same room overlap in time, which is a genuine cross-row invariant the database will enforce for you.
Adding one to a large table without an outage
Validating a new constraint scans the whole table while holding a strong lock. The two-step form avoids it: add the constraint NOT VALID so it applies to new writes immediately and takes only a brief lock, then run VALIDATE CONSTRAINT separately, which scans with a weaker lock that does not block writes.
Implementing it
Put every invariant the business actually depends on in the database, and treat application validation as the user-experience layer rather than the enforcement layer. If a rule being violated would be a serious incident, it belongs in a constraint.
Expect constraint violations in normal operation and translate them into meaningful responses. A unique violation on an idempotency key is not an error — it is the mechanism working, and the correct response is to return the original result.
Use the two-step NOT VALID then VALIDATE pattern for any constraint added to a table with traffic, and set a short lock_timeout so a blocked migration fails fast instead of queueing every reader behind it.
Reach for partial unique indexes and exclusion constraints before writing application code that checks-then-writes. Most "only one active X per Y" rules are a one-line partial index.
-- Race-prone: two concurrent callers both pass the check, both insert.
SELECT 1 FROM users WHERE email = $1;
INSERT INTO users (email) VALUES ($1);
-- The index is the enforcement. The second insert fails, atomically.
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
-- Conditional rule, enforced rather than hoped for:
-- at most one active subscription per user.
CREATE UNIQUE INDEX one_active_sub_per_user
ON subscriptions (user_id) WHERE status = 'ACTIVE';
-- A genuine cross-row invariant: no two bookings for a room may overlap.
ALTER TABLE bookings ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);
-- Adding to a large table without blocking writes for the validation scan
ALTER TABLE orders ADD CONSTRAINT amount_positive
CHECK (amount_minor > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT amount_positive;Interview questions and how to answer them
Why is a unique constraint better than checking in the application?
Because the check and the insert are separate events with a gap between them, and under concurrency both callers pass the check before either inserts. The constraint is enforced atomically at write time by the index, so one insert simply fails. It also applies to every writer — other services, migrations, manual fixes — none of which run your validation.
What does a foreign key cost?
A lock on the referenced parent row when inserting a child, to prevent it being deleted mid-transaction. That is contention on a hot parent and a deadlock risk when transactions acquire parents in different orders. It also means deleting a parent needs an index on the child's referencing column, or the check degrades to a scan.
How would you add a NOT NULL to a large production table?
Not in one step — the validating scan holds a strong lock. Add a CHECK (col IS NOT NULL) NOT VALID first, which applies to new rows immediately with only a brief lock, backfill existing rows in batches, then VALIDATE CONSTRAINT, which scans under a weaker lock. On recent PostgreSQL the validated check can then be converted to a real NOT NULL cheaply.
Can a CHECK constraint reference another table?
No — it is evaluated per row from that row's own values. Cross-row and cross-table invariants need something else: a foreign key for existence, an exclusion constraint for non-overlap, or a trigger. People reach for a trigger first when an exclusion constraint would have been declarative and cheaper.
Should constraints be deferrable?
Only when you genuinely need to violate the invariant temporarily within a transaction — circular references being the classic case, or swapping two unique values. Deferring moves the check to commit time, which means the failure arrives further from the statement that caused it and is harder to attribute. Default to immediate.
Answers that lose the round
- Believing an application-level check enforces uniqueness — it cannot, under any concurrency
- Omitting foreign keys "for performance" and accumulating orphaned rows nobody notices
- Treating a unique violation as an unexpected error rather than an expected outcome to handle
- Adding a validated constraint to a large table during traffic and blocking every reader
- Writing application code for "only one active X" where a partial unique index expresses it
- Not knowing that inserting a child row locks the parent, then being surprised by contention
- Catching a constraint violation and continuing, having discarded a write while reporting success
Practise database constraints in a real repository
Gronex ships the check-then-write failure as a runnable repository: inventory that oversells under parallel buyers. The tests assert stock never goes negative, so moving the check around in application code does not pass — only enforcing the invariant does.
FAQ
Do constraints slow down writes?
Measurably but usually modestly, and the comparison is not against nothing — it is against the application doing an extra round trip to check the same thing less reliably. A unique constraint also gives you an index you probably wanted. The cost worth watching is foreign key lock contention on hot parent rows.
What about microservices where each owns its data?
Within a service's own database, use them fully. Across services you cannot have a foreign key, and the honest consequence is that referential integrity becomes an eventual-consistency problem requiring reconciliation — which is a real cost of splitting, and one worth naming when someone proposes it.
Should I rely on ORM-level validations instead?
Use them for user experience, not for correctness. They run only when writes go through that ORM path, so a raw query, a second service or a migration bypasses them silently. The database constraint is the one rule nobody can route around.
How do I return a good error message from a constraint violation?
Catch the violation and map the constraint name to a human message. Name constraints deliberately for this reason — users_email_key is mappable, a system-generated name is not. Note the transaction is aborted after the violation, so you need a savepoint if you intend to continue.