ValidationGENERALDATABASE-SPECIFICSCALE-SPECIFIC

Database Constraints

The only check evaluated inside the write — which is why it is the only one that survives two requests arriving at the same instant.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

Why is "email must be unique" a database problem rather than an application one?

The requirement

Two people must never end up with the same account email. The product has 40 signups a day, so this has never been an issue.

The obvious build

SELECT id FROM users WHERE email = $1 — if nothing comes back, INSERT. It reads clearly, gives a friendly error message, and every test passes.

Why it breaks

Two signups arrive 8 ms apart. Both SELECTs run before either INSERT commits, both find nothing, both insert. There are now two users with the same email and no line of code that was wrong.

How it breaks in production

Two signups arrive 8 ms apart. Both SELECTs run before either INSERT commits, both find nothing, both insert. There are now two users with the same email and no line of code that was wrong.
The reproduction is not exotic: a double-clicked submit button sends two requests, and a client retry after a timeout sends a second one after the first is already executing (Duplicate Detection).
The bug is invisible in staging, where nobody clicks twice, and in load tests, which use unique emails.
Login then breaks — findUnique returns one of two rows, non-deterministically, so the user "sometimes" gets the wrong account.
The cleanup is manual, because merging two accounts that both have data is a product decision, not a migration.
Adding the unique index later fails: the table already contains the duplicates the missing index allowed.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

The application's check and its write are two statements with a gap between them. Under READ COMMITTED — the default in Postgres and effectively in MySQL — a plain SELECT takes no lock, so nothing prevents another transaction committing into that gap (Isolation Levels).
A unique index is enforced by the storage engine as part of the insert. The second writer is blocked by the index entry itself and fails, atomically, with no gap to exploit.
The engine can do this because it holds the index page; the application cannot, because between its SELECT and its INSERT it holds nothing at all.
A constraint violation arrives as an error with a code: 23505 (unique) or 23503 (foreign key) in Postgres, 1062 / 1452 in MySQL. Handling it means catching that specific code, not any error.
INSERT ... ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE let one statement express insert-or-not, closing the gap without an application-level lock (Atomic Operations).
A raised constraint aborts the transaction in Postgres: after the error, every subsequent statement in that transaction fails until rollback or a savepoint. Retrying in place without one does not work.
Constraints also enforce rules that are not about duplicates: foreign keys stop orphans, CHECK stops negative balances, NOT NULL stops absent required data, and Postgres EXCLUDE stops overlapping ranges — a booking-conflict rule as one declaration.

The gap, drawn

The interleaving is worth seeing as a timeline rather than as prose, because the surprising part is that neither transaction did anything wrong. Each read a consistent state and acted on it. The rule was violated by the *combination*, which is exactly the class of bug the application layer cannot see.

Every attempt to fix this in application code — re-check before insert, check again after, add a mutex in the process — either leaves a smaller gap or moves the problem to a lock that is not shared across instances (Stateless Services).

time   T1 (signup, alice@x.com)          T2 (signup, alice@x.com)      state
────────────────────────────────────────────────────────────────────────────────
 t0    BEGIN                                                            0 rows
 t1    SELECT id WHERE email=$1  -> none                                0 rows
 t2                                        BEGIN
 t3                                        SELECT id WHERE email=$1      0 rows
 t4                                          -> none   <-- the gap
 t5    INSERT INTO users ...                                            1 row
 t6    COMMIT                                                           1 row
 t7                                        INSERT INTO users ...
                                             WITHOUT unique index: OK   2 rows  <-- bug
                                             WITH    unique index: 23505 1 row  <-- correct
 t8                                        COMMIT / ROLLBACK

Both transactions read a consistent state. READ COMMITTED takes no lock on a
plain SELECT, so nothing existed at t4 for T2 to conflict with. The index entry
created at t5 is the first thing in the system that T2 can collide with — and
it only exists if someone declared it.

The pre-check stays; the constraint decides

DATABASE-SPECIFICPostgres syntax throughout. MySQL 8 has no partial indexes (emulate with a generated column that is NULL when the condition is false, since multiple NULLs do not collide) and uses INSERT ... ON DUPLICATE KEY UPDATE, which cannot express "do nothing and tell me". CREATE INDEX CONCURRENTLY is Postgres-specific and cannot run inside a transaction; MySQL 8 does most ALTERs online by default instead.

The correct arrangement is not "constraint instead of check". It is both, with each doing the job it is capable of: the query produces a good message for the 99.9% of duplicates that arrive seconds apart, and the constraint handles the 0.1% that arrive milliseconds apart — plus every future code path that forgets to check at all.

The second block below removes the gap entirely by making insert-or-not a single statement. Where it fits, it is better than catching an error, because there is no aborted transaction to recover from.

Declare it, then handle it

1-- semantics decided in the schema: case-insensitive, tenant-scoped
2CREATE UNIQUE INDEX CONCURRENTLY users_org_email_lower_uidx
3  ON users (org_id, lower(email));
4 
5-- conditional uniqueness: one active subscription per user, any number of cancelled
6CREATE UNIQUE INDEX subs_one_active_uidx
7  ON subscriptions (user_id) WHERE status = 'active';
8 
9-- an aggregate rule the application cannot enforce race-free on its own
10ALTER TABLE accounts ADD CONSTRAINT seats_within_plan
11  CHECK (seats_used >= 0 AND seats_used <= seats_limit);
12 
13-- no gap at all: one statement decides, and tells you which happened
14INSERT INTO users (org_id, email, name)
15VALUES ($1, $2, $3)
16ON CONFLICT (org_id, lower(email)) DO NOTHING
17RETURNING id;
18-- zero rows returned == someone else already has it. No error, no retry, no race.

The RETURNING on an ON CONFLICT DO NOTHING is the useful trick: an empty result *is* the answer, so the duplicate case never becomes an exception and the transaction is never aborted.

Reading the error, and answering the caller

A constraint you do not handle converts a routine user mistake into a 500 and an alert. The handling is short, and the two things that matter are matching on the code rather than the message, and mapping the constraint *name* to a field the client can act on.

This is also where the trust boundary and the response contract meet: the violation tells you a great deal about your schema and the caller must learn none of it (Not Leaking Your Internals).

Catch the code, not the string

1const CONSTRAINT_TO_ERROR: Record<string, { status: number; code: string; field?: string }> = {
2  users_org_email_lower_uidx: { status: 409, code: 'email_taken', field: 'email' },
3  subs_one_active_uidx:       { status: 409, code: 'subscription_already_active' },
4  seats_within_plan:          { status: 409, code: 'seat_limit_reached' },
5  orders_customer_id_fkey:    { status: 422, code: 'unknown_customer', field: 'customerId' },
6}
7 
8try {
9  return await insertUser(tx, cmd)
10} catch (err) {
11  if (!isPgError(err)) throw err
12  // 23505 unique_violation, 23503 foreign_key_violation, 23514 check_violation
13  if (!['23505', '23503', '23514'].includes(err.code)) throw err
14 
15  const mapped = CONSTRAINT_TO_ERROR[err.constraint ?? '']
16  if (!mapped) throw err                       // unmapped == a rule with no owner: page us
17 
18  logger.info({ constraint: err.constraint, code: mapped.code }, 'constraint rejected write')
19  return { ok: false, status: mapped.status, body: { code: mapped.code, field: mapped.field } }
20  // NOT err.detail, NOT err.message — both contain the value and the index name
21}

Rethrowing an unmapped constraint is deliberate: a violation nobody mapped is a rule with no handler, and it should be loud. Note also that this must run *outside* the aborted transaction — in Postgres, every statement after a violation fails until rollback or a SAVEPOINT is released.

How to build it

Most important first.

Put a real constraint behind every rule of the form "no two of these" or "this must reference something that exists". If it must be true, the engine has to be the one enforcing it.
Keep the application pre-check too, and understand its role: it gives a good message for the overwhelming majority of duplicates that are not races. It is user experience; the constraint is enforcement.
Catch the specific error code and map it to a meaningful response — 409 with a stable code — rather than letting it become a 500 (Reporting Validation Failures).
Prefer a single statement where one exists. INSERT ... ON CONFLICT DO NOTHING RETURNING id with an empty result means "someone else got there", with no race and no retry.
Decide the *semantics* in the schema, not in code: uniqueness on lower(email) or a citext column, so Alice@x.com and alice@x.com cannot both exist (Normalization: 1NF to BCNF).
Use partial indexes for conditional uniqueness — one active subscription per user, unlimited cancelled ones: CREATE UNIQUE INDEX ... WHERE status = 'active'.
Add constraints with expand-and-contract: create the index concurrently, fix the existing violations, then enforce. Adding one to dirty data fails or locks the table (Expand and Contract Migrations, Schema Migrations from the Application Side).
Name your constraints. The default name is what you will be pattern-matching in the error handler, and a rename in a later migration silently breaks that handler.

What can go wrong

Failure modes

The constraint exists and nothing catches the violation, so ordinary duplicate signups are 500s and page someone (Error Boundaries: Three Translations, Not One).
The handler matches on the error message string rather than the code, and a driver or engine upgrade changes the wording.
A retry loop around a Postgres transaction that has already aborted — every statement after the violation fails until rollback or SAVEPOINT.
ON CONFLICT DO UPDATE used as an upsert without a WHERE, so a concurrent update is silently overwritten — a lost update wearing a safe-looking statement (Optimistic Concurrency).
A NOT VALID check constraint added and never validated, so it applies to new rows only and everyone believes it applies to all.
A unique index on a very hot insert path becoming a contention point on its rightmost pages when the key is monotonic (Composite Indexes and the Leftmost-Prefix Rule).
Foreign keys with ON DELETE CASCADE where RESTRICT was meant, so deleting one row removes far more than intended.

What can race

Check-then-insert: T1 SELECT (none) → T2 SELECT (none) → T1 INSERT → T2 INSERT. Both transactions were internally consistent; the result violates the rule. Only a unique index makes T2 fail.
Check-then-update: reading a status and then updating on the id alone loses to any concurrent writer. WHERE id = $1 AND status = $2 plus a row-count check converts the race into a detectable conflict (Optimistic Concurrency).
Aggregate rules ("at most five seats") race on the aggregate and no per-row constraint catches them. A denormalised counter with a CHECK (seats_used <= seats_limit) updated in the same statement does (Atomic Operations).
Foreign key versus concurrent delete: a row referenced during insert can be deleted concurrently. The FK turns that into a violation rather than an orphan — which is the point, and it means the insert can fail for reasons unrelated to its own input.
Deferred constraints in Postgres are checked at commit, so a violation appears at COMMIT rather than at the statement — useful for circular references, and surprising for error handling that assumes statements fail where they are written.

Security

Constraints are the last line of defence in depth: they hold even when a code path skips validation entirely — a migration script, an admin console, a bulk import (Defence in Depth).
The violation error must not reach the client. duplicate key value violates unique constraint "users_email_lower_idx" discloses your schema (Not Leaking Your Internals).
A uniqueness check is a user-enumeration oracle on public signup: "that email is taken" tells an attacker who has an account. That is a deliberate product tradeoff, not an oversight to fix blindly (Broken Access Control (IDOR / BOLA)).
Tenant-scoped uniqueness must include the tenant in the key: UNIQUE (tenant_id, slug), not UNIQUE (slug) — the second leaks the existence of other tenants' resources through collisions (Tenant Isolation).

Misreads

"We check for duplicates in code, so a unique index is redundant." The code check cannot be atomic with the write. It is the index or it is a race.
"It has never happened, so it is fine." It has never been *observed*. Duplicate rows do not raise alerts; they surface later as a login that returns the wrong account.
"Constraints are slow." A unique index costs a write-time index maintenance. The alternative costs an extra SELECT per signup and does not work.
"Business logic belongs in the application, not the database." A reasonable principle that does not apply here: this is not logic, it is the only place atomicity exists (The Three Validations).
"ON CONFLICT DO UPDATE makes it idempotent." It makes the write not fail. Whether the operation is idempotent depends on what else it does — emails, charges, events (Idempotency in Backends).
"Serializable isolation solves it, so I do not need constraints." It does close the gap, at the cost of serialization failures every caller must retry, and it does not help the code path that skipped your validation entirely (Isolation Levels).

Operating it

How you see it in production

Count violations by constraint name. A steady low rate is normal user behaviour; a step change is a client bug or an attack.
The ratio of constraint violations to application-level pre-check rejections is a direct measurement of how often you are racing. Rising ratio means concurrency has arrived (Backend Races).
Alert on 500s that carry a constraint error code. Each one is a rule with no handler.
Before adding a constraint, run the violating query and count. That number is the migration plan (Expand and Contract Migrations).

What changes at 10x and 100x

The check-then-insert bug does not appear gradually. It is absent until two requests overlap on the same key, then it is a support ticket a week, then daily. Traffic changes only the frequency, never the correctness.
At 100x writes, unique index maintenance is real write amplification, and a monotonic key concentrates it on one part of the index (Write, Read and Space Amplification).
Foreign keys cost a lookup per write and take locks on the referenced row. At very high write rates some teams drop them and enforce referentially in the application — a deliberate trade of integrity for throughput, and one that should be written down as such (Denormalization on Purpose).
Cross-shard or cross-service uniqueness is not a constraint problem at all: no engine can enforce it, so you need a single owning store for the key or a reservation protocol (Eventual Consistency in Practice).

What this costs

Constraints are schema, so changing one is a migration with locking behaviour to plan, not a deploy.
The errors are unhelpful by design. Turning them into good messages is application work you must actually do.
Every index costs write throughput and storage. A uniqueness rule you do not need is a permanent tax.
Strict foreign keys make test fixtures and data cleanup harder, which is a real productivity cost paid in exchange for never having orphan rows.
ON CONFLICT hides whether a row was inserted or already existed unless you ask, and code that does not ask often needs to know (Idempotency in Backends).

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALThe check-then-act gap and the fact that only an in-write check closes it are true of every relational engine, and of any store with a compare-and-set primitive.
DATABASE-SPECIFICWhat you can express and what you must catch both differ. Postgres: 23505/23503 SQLSTATEs, partial and expression indexes, EXCLUDE with GiST for overlap rules, DEFERRABLE constraints checked at commit, and an aborted transaction after any violation. MySQL/InnoDB: errors 1062/1452, no partial indexes (use a generated column), CHECK enforced only from 8.0.16, ON DUPLICATE KEY UPDATE instead of ON CONFLICT, and unique indexes that treat multiple NULLs as distinct just as Postgres does. SQLite: foreign keys are off unless PRAGMA foreign_keys=ON, and RETURNING needs 3.35+. A constraint that is one line on one engine can be a trigger or application code on another.
SCALE-SPECIFICFlips on concurrent writes to the same key, which is not the same as traffic. A product doing one signup a minute with unique emails will never observe the race; the same code with a double-submitting form observes it on day one. The threshold is whether two requests can be in flight against one key within the check-to-write window — tens of milliseconds — so client retries and duplicate webhooks put you above it at any request rate.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designidempotency-keys status-codes

Databaserelationships-and-keys isolation-levels concurrency-anomalies transactions-and-acid composite-indexes normalization

Securitydefense-in-depth multi-tenant-isolation

Concurrencyoptimistic-concurrency-control database-concurrency reasoning-about-races