Constraints
🏠The everyday picture
A ticket machine that refuses torn notes, won't sell a seat that's already sold, and won't print a ticket without a date. The rules are built into the machine, so no clerk can forget them.
📘What it really is
Constraints are rules the database enforces on every change, from every program, forever:
NOT NULL: a value is required (name text NOT NULL);UNIQUE: no duplicates (email text UNIQUE);CHECK: a condition must hold:
amount_paise integer NOT NULL CHECK (amount_paise > 0),
status text NOT NULL CHECK (status IN ('new', 'in-progress', 'closed'))
DEFAULT: a value used when none is given (created_at timestamptz NOT NULL DEFAULT now());PRIMARY KEYandFOREIGN KEY(Module 53).
Why constraints matter even when your API validates (Module 43):
- other programs (scripts, admin tools, a future second app) may write to the database;
- bugs happen; constraints are the last line of defence;
- they make the meaning of the data explicit.
Validate in the API for friendly messages, and constrain in the database for guarantees.
🖱️Try it
Add constraints to your tables: names required, emails unique, payment amounts positive, method limited to
'cash', 'upi' and 'card', created_at defaulting to the current time. Try to break each rule with an INSERT and
read the error message.
✅Quick check
The API already validates payment amounts. Why add a CHECK constraint too?
Show the answer
Other programs or bugs could still write bad data; the database guarantees the rule for every write.
What does DEFAULT now() do on a created_at column?
Show the answer
Fills in the current time automatically when a row is inserted without a value.