Constraints: rules the database enforces
🎯You'll be able to
Write constraints that keep bad data out even when application code has a bug.
🏠The everyday picture
A railway reservation chart has numbered berths. However busy the counter is, the chart can't hold two names on berth 23. The rule lives in the chart, not in the clerk's memory.
📘What it really is
-
Application checks are useful, but every code path, script and future developer must remember them. A constraint is checked by the database on every write, from anywhere.
-
The main kinds:
NOT NULL: the value must be present;CHECK (qty BETWEEN 1 AND 20),CHECK (price_paise > 0): the value must make sense;UNIQUE (customer_id, key): no duplicates of this combination;FOREIGN KEY: the thing pointed at must exist;-
EXCLUDE(PostgreSQL): no overlaps, such as two bookings for one room at overlapping times. -
Constraints turn race conditions into clean errors: two inserts with the same unique key can't both succeed (BE1 Lesson 4).
- Catch constraint errors in code and turn them into helpful 4xx responses (
409 Conflictfor a duplicate), not 500s. - Keep both: checks in code give friendly messages early; constraints are the last line of defence.
📝A worked example
TiffinBox sells a limited number of tiffins per kitchen each day. A table and constraint:
CREATE TABLE daily_capacity (
kitchen_id int REFERENCES kitchens(id),
day date,
capacity int NOT NULL CHECK (capacity >= 0),
booked int NOT NULL DEFAULT 0,
PRIMARY KEY (kitchen_id, day),
CHECK (booked <= capacity)
);
Input: kitchen 4 has capacity 50 and booked 50; an order runs UPDATE daily_capacity SET booked = booked + 1 WHERE kitchen_id = 4 AND
day = '2026-11-02';. Expected output: an error from the check booked <= capacity. The handler turns it into 409 Conflict: Kitchen 4
is full for 2 November.
🌍Where it's used
- Seat, slot and stock limits in booking and shopping systems.
- Financial records, where amounts must be positive and accounts must exist.
- Data imports and admin scripts, which skip the app's checks.
📝Case scenario
A support engineer runs a quick SQL fix to correct a customer's order and types qty = -2. The app's validation never ran, and the
monthly report shows negative sales. A CHECK (qty BETWEEN 1 AND 20) would have rejected the fix at once, with an error the
engineer could act on.
🖱️Try it
In psql, try three inserts that break the kit's constraints: a 0 quantity, a negative price and a duplicate customer sub. Then add
the daily_capacity table above and try to overbook. Success: four clear errors, each naming its constraint.
⚠️Common mistakes
- Rules only in application code. Sign: bad rows appear from scripts, imports or a second service. Fix: add the matching constraint.
- Letting constraint errors become 500s. Sign: users see "server error" when a slot is full. Fix: catch the specific error code (such as
23505for unique) and return a 4xx. - No name on constraints. Sign: errors say
orders_check1. Fix: name them (CONSTRAINT qty_range CHECK (...)) so errors explain themselves.
🛠️For your project
Project 1's idempotency relies on the request_keys primary key; Project 3's repeat-safety relies on processed_messages.
✅Quick check
Why add a constraint when the code already validates the value?
Show the answer
Because data can also arrive from scripts, imports, other services or future code that skips the check.
What should an API return when a unique constraint rejects a duplicate?
Show the answer
A 4xx, usually 409 Conflict, or the earlier result for an idempotent repeat, not a 500.