TopolearnApplication checks get bypassed by scripts, bugs and race conditions; constraints don't.
A schema is not just storage; it is a set of rules every row must obey. Declaring those rules as constraints means the database rejects bad data no matter which code path wrote it — the web app, a cron job, or a person in a SQL console.
NOT NULLfor every column that must always have a value. Make nullable the exception you justify.CHECKfor row-level rules:quantity > 0,ends_at > starts_at,status IN ('new','paid','shipped').UNIQUEfor "only one of these": one account per email, one active cart per user (with a partial unique index in databases that support it).- Foreign keys so references always point to existing rows; choose
ON DELETEbehaviour (RESTRICT,CASCADE,SET NULL) deliberately.
Why not validate only in the application? Because two requests can both check "is this email free?" and both insert. Only a unique constraint closes that race: one insert succeeds, the other gets an error you can turn into a friendly message.
Constraints also document intent. A new developer reading CHECK (discount BETWEEN 0 AND 100) learns a business rule without hunting through code. The cost is small: a little work on each write, which is almost always worth it.
Example
CREATE TABLE booking (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room int NOT NULL,
starts_at timestamptz NOT NULL,
ends_at timestamptz NOT NULL,
status text NOT NULL DEFAULT 'new'
CHECK (status IN ('new', 'confirmed', 'cancelled')),
CHECK (ends_at > starts_at)
);
INSERT INTO booking (room, starts_at, ends_at)
VALUES (101, '2026-05-01 14:00+00', '2026-05-01 12:00+00');
-- ERROR: new row for relation "booking" violates check constraint
-- "booking_check"Remember
- Constraints are enforced for every writer, not just your app
- Only a UNIQUE constraint safely prevents concurrent duplicates
- Default to NOT NULL; allow NULL only when 'unknown' is a real state
Watch out for
- Relying on a SELECT-then-INSERT check in code to prevent duplicates.