SQL Constraints as Concurrent Safety Nets

Most business rules start life as an if statement: “if the email is already taken, show an error”, “if the room is booked, refuse”. That works in a demo with one user. In production, two requests arrive within the same few milliseconds, both run the if, both see the coast is clear, and both write. Constraints are how you move a rule from “the code usually checks this” to “the database won’t store data that breaks it”.

This guide uses a meeting-room booking table and runs everything on PostgreSQL 16.15, including a real two-session race.

Watching check-then-insert fail

Here’s the table and the application’s logic written as SQL. The transaction first checks for an overlapping booking, then inserts. The pg_sleep(2) stands in for whatever the app does between those steps (a network hop, a pricing call, garbage collection).

CREATE TABLE bookings (
  id      serial PRIMARY KEY,
  room_id int NOT NULL,
  during  tstzrange NOT NULL
);

BEGIN;
SELECT count(*) AS clashes
FROM bookings
WHERE room_id = 7
  AND during && tstzrange('2026-11-03 09:00+00', '2026-11-03 10:00+00');
SELECT pg_sleep(2);
INSERT INTO bookings (room_id, during)
VALUES (7, tstzrange('2026-11-03 09:30+00', '2026-11-03 10:30+00'));
COMMIT;

Two psql sessions ran that script 0.3 seconds apart. Both printed clashes = 0, both committed, and the table ended up like this:

 id | room_id |                       during
----+---------+-----------------------------------------------------
  1 |       7 | ["2026-11-03 09:30:00+00","2026-11-03 10:30:00+00")
  2 |       7 | ["2026-11-03 09:30:00+00","2026-11-03 10:30:00+00")

Room 7 is double-booked. Nothing went wrong in the usual sense: no error, no deadlock. Under PostgreSQL’s default Read Committed isolation, each SELECT sees rows committed before it started, and neither session’s row was committed yet. Wrapping the check in a transaction doesn’t help, which surprises a lot of candidates. A transaction gives you atomicity for your own writes; it doesn’t stop someone else from inserting a row your earlier SELECT didn’t see.

You could fix this with locks (SELECT ... FOR UPDATE on the room row, or an advisory lock) or by running at Serializable. Those are valid, but every code path that writes bookings has to remember to do it. A constraint is checked for every writer: the API, the admin script, the migration someone runs at 2 a.m.

Turning the rule into a constraint

“No two bookings for the same room may overlap” is an exclusion constraint. It says: for any two rows, it must not be true that room_id is equal and during overlaps.

CREATE EXTENSION btree_gist;   -- lets a GiST index handle plain = on int
ALTER TABLE bookings
  ADD CONSTRAINT no_double_booking
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

btree_gist ships with PostgreSQL as a contrib extension; it’s needed because GiST indexes don’t support = on integers out of the box. Now try the overlapping insert after a 09:00–10:00 booking exists:

ERROR:  conflicting key value violates exclusion constraint "no_double_booking"
DETAIL:  Key (room_id, during)=(7, ["2026-11-03 09:30:00+00","2026-11-03 10:30:00+00")) conflicts with existing key (room_id, during)=(7, ["2026-11-03 09:00:00+00","2026-11-03 10:00:00+00")).

A 10:00–11:00 booking right after it is accepted, because tstzrange is half-open by default ([start, end)), so ranges that touch at 10:00 don’t overlap. That’s a detail worth mentioning, since it’s exactly how back-to-back meetings should behave.

The concurrent case is the important one. With the constraint in place, the two-session test changes: the second session’s INSERT waits until the first commits (about 1.7 seconds here), then fails with the same error. The database serialises the two writers on the index entry, which is the part application code can’t do by itself.

The other constraints and what each one protects

• NOT NULL says a value must exist. It’s the cheapest constraint and the one most often forgotten on columns that “always” get set.

• UNIQUE / primary key stops duplicates, and is backed by a unique B-tree index. It also works on expressions through a unique index, which is how you make emails case-insensitively unique:

  CREATE UNIQUE INDEX users_email_lower ON users (lower(email));
  -- inserting 'ana@example.com' after 'Ana@Example.com':
  -- ERROR:  duplicate key value violates unique constraint "users_email_lower"
  -- DETAIL:  Key (lower(email))=(ana@example.com) already exists.

• CHECK validates one row in isolation:

  CREATE TABLE accounts (id int PRIMARY KEY,
                         balance numeric NOT NULL CHECK (balance >= 0));
  UPDATE accounts SET balance = balance - 80 WHERE id = 1;  -- balance was 50
  -- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
  -- DETAIL:  Failing row contains (1, -30).

  A useful side effect: two concurrent withdrawals that each pass an application-level balance check still can’t drive the balance negative, because the UPDATE ... SET balance = balance - 80 re-reads the current value under a row lock and the check runs on the result.

• Foreign keys stop orphans: you can’t insert a booking for a room that doesn’t exist, or delete a room that still has bookings (unless you choose ON DELETE CASCADE or SET NULL).

• Exclusion constraints generalise uniqueness to “no two rows may conflict under these operators”, which covers overlaps and adjacency rules that UNIQUE can’t express.

What constraints can’t do: a CHECK can’t look at other rows or other tables. “A customer may have at most three active subscriptions” is not a CHECK constraint, however tempting it is to call a function from one. PostgreSQL’s docs warn that a CHECK referencing other rows isn’t re-checked when those rows change, so it gives a false sense of safety. Rules like that need a lock on the parent row, a counter column with a CHECK, a trigger with proper locking, or Serializable isolation.

Keep the application check, change its job

Don’t delete the if in your code. It’s still the best place to produce “That room is already booked from 9:00 to 10:00, try 10:00?” instead of a raw database error. Its job changes from enforcement to user experience. The constraint is the enforcement, and the code that writes must handle the constraint error as a normal outcome:

• Exclusion violations have SQLSTATE 23P01, unique violations 23505, check violations 23514, foreign key violations 23503. Match on the code and constraint name, not the message text.
• Map them to a 409 Conflict or a validation error, not a 500.
• Name constraints deliberately (no_double_booking, not bookings_room_id_during_excl) so the error is readable in logs and the mapping in code is obvious.

For uniqueness specifically, INSERT ... ON CONFLICT lets you decide the outcome in the statement itself; see PostgreSQL upserts and conflict semantics. If you’re debugging the unique error itself, the duplicate key guide covers causes like out-of-sync sequences.

Adding a constraint to a table that already has data

On a large table, ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) scans every row while holding a lock that blocks writes. The usual approach is two steps: add it with NOT VALID, which enforces the rule for new writes immediately without scanning, then run ALTER TABLE ... VALIDATE CONSTRAINT ... later, which scans with a weaker lock that allows normal reads and writes. Foreign keys support the same pattern. Unique constraints are different: build the index with CREATE UNIQUE INDEX CONCURRENTLY, then attach it with ADD CONSTRAINT ... UNIQUE USING INDEX. Existing bad rows have to be cleaned up first, and finding them is often the real project.

Interview practice: the coupon that was redeemed twice

A coupon is meant to be single-use per customer. The code does SELECT 1 FROM redemptions WHERE coupon_id = $1 AND customer_id = $2, and inserts if nothing comes back. Support found customers with two redemptions of the same coupon, a few milliseconds apart. Explain what happened and how you’d fix it.

Answer

It’s the check-then-insert race. A double-click or a client retry sent two requests; both ran the SELECT before either inserted, both found nothing, both inserted. The transaction around the two statements doesn’t prevent it at Read Committed, because neither transaction can see the other’s uncommitted row.

The fix is UNIQUE (coupon_id, customer_id) on redemptions. First find and resolve existing duplicates (decide which redemption stands, refund or reverse the other), then create the unique index concurrently and attach it as a constraint. In the code, either use INSERT ... ON CONFLICT (coupon_id, customer_id) DO NOTHING RETURNING id and treat “no row returned” as “already redeemed”, or catch SQLSTATE 23505 and return a friendly message. I’d keep the pre-check for the nicer error message but stop relying on it. If the rule were “at most three redemptions” instead of one, UNIQUE can’t express it, so I’d lock the coupon row with SELECT ... FOR UPDATE before counting, or run the transaction at Serializable and retry on serialization failures.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• Duplicate key value violates unique constraint
• PostgreSQL row locks and concurrent updates
• Official docs: PostgreSQL constraints