The database is the last honest gate

Application code is a suggestion; the schema is a rule

A validation in your service protects the rows written by that service, in that version, on that code path. The database protects every row written by anything: the old version still running during a deploy, the migration script, the admin console, the analyst with psql, the batch job someone wrote in 2021, and whatever was generated last week and reviewed quickly.

That has always been the argument for constraints. It got stronger in exactly the way everything else in this module did: more write paths, less scrutiny per path, and a single guarantee that covers all of them for one line of DDL.

The constraints that earn their place

NOT NULL, on everything that cannot be absent. Free, checked always, and the single most common source of NoneType has no attribute at 3am.

CHECK, for the domain rules that are expressible.

ALTER TABLE charges
  ADD CONSTRAINT amount_non_negative CHECK (amount_minor >= 0),
  ADD CONSTRAINT currency_is_iru CHECK (currency ~ '^[A-Z]{3}$');

A CHECK (amount_minor >= 0) catches a whole class of sign bugs for ever, regardless of what generated the code above it, and it will find one within a month in most systems that add it.

UNIQUE, including partial and functional forms, which people underuse:

-- one active subscription per customer; cancelled ones may repeat
CREATE UNIQUE INDEX one_active_sub
  ON subscriptions (customer_id)
  WHERE status = 'active';

-- emails unique case-insensitively
CREATE UNIQUE INDEX users_email_ci ON users (lower(email));

That first one replaces an application-level check that has a race condition in it. There is no way to do it correctly in application code without a lock, and there is no way to do it incorrectly with that index.

Foreign keys, with the deletion behaviour stated: ON DELETE RESTRICT for anything you must not lose, ON DELETE CASCADE only where you mean it. Choosing cascade by accident is how a support agent deletes a customer and takes four years of invoices with them.

Exclusion constraints, in PostgreSQL, for the class of rule nothing else expresses:

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_double_booking
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

No two bookings for the same room may overlap in time. Try writing that correctly in application code under concurrency. This is one line and it cannot be violated.

Generated columns, so a derived value cannot drift from its source:

ALTER TABLE invoices
  ADD COLUMN total_minor bigint
  GENERATED ALWAYS AS (subtotal_minor + tax_minor) STORED;

Adding a constraint to data that already violates it

This is the part that stops people, and it has a standard answer.

1. Find the violations first. SELECT count(*) FROM charges WHERE amount_minor < 0; If it is zero, you are adding a guarantee. If it is 4,812, you have just discovered a bug that has been running for two years, and that discovery is the main value of the exercise.
2. Decide what those rows mean. Fix them, delete them, or move them somewhere quarantined. This is a data decision and it needs a human.
3. Add the constraint without a long lock. In PostgreSQL:

ALTER TABLE charges ADD CONSTRAINT amount_non_negative
  CHECK (amount_minor >= 0) NOT VALID;   -- instant, applies to new rows
ALTER TABLE charges VALIDATE CONSTRAINT amount_non_negative;  -- scans, weak lock

The NOT VALID step takes microseconds and starts protecting immediately. The validation scan takes as long as it takes and does not block writes. Doing it in one step on a large table takes an exclusive lock and is how people cause an outage while improving safety.

Isolation levels, briefly, because it is the classic silent bug

Read-modify-write in application code under the default isolation level is a race, always. SELECT balance; if balance >= amount; UPDATE balance = balance - amount runs correctly a thousand times and double-spends under load.

The fixes, cheapest first: do the arithmetic in SQL (UPDATE ... SET balance = balance - $1 WHERE balance >= $1, then check the affected row count); take a row lock (SELECT ... FOR UPDATE); or raise the isolation level and retry on serialisation failure.

This is worth knowing precisely because it is invisible to review. The code reads correctly. It is wrong about concurrency, and concurrency is exactly the category where plausible-looking code is most dangerous.

The one-line summary

Every constraint you add is a class of bug that becomes impossible rather than merely unlikely, enforced against every writer for ever, for the price of one line and one migration. In a period when the number of writers went up and the attention per writer went down, that is the best trade available in the whole of this course.