Database constraints are the only documentation that cannot go stale

Every codebase has a comment that is no longer true. It described the behaviour accurately when it was written, and then the behaviour changed and the comment did not.

Database constraints cannot do that. A NOT NULL is not a description of the rule; it is the rule. If it were no longer true, the write would have failed.

#What belongs in the schema

Referential integrity. If an order must belong to a customer, that is a foreign key. Enforcing it only in application code means the first buggy migration script, admin query or background job that skips your ORM leaves orphans behind, and you find out months later while debugging something unrelated.

Nullability. Whether a column can be absent is a domain fact. Deciding it in the schema forces the question at design time, which is when it is cheap to answer.

Uniqueness. A unique index is the only reliable way to enforce uniqueness under concurrency. A check-then-insert in application code is a race condition with a comfortable disguise — it passes every test that runs one request at a time.

Enumerable states. Whether via a native enum or a check constraint, the set of legal statuses belongs where the data lives.

ALTER TABLE "Project"
  ADD CONSTRAINT project_status_valid
  CHECK (status IN (
    'planning','design','development','testing',
    'deployment','maintenance','completed','on_hold'
  ));

Now an unknown status cannot exist, no matter which code path wrote it. Compare that with a TypeScript union, which is checked at compile time and erased at runtime — genuinely useful, but no help at all against a manual UPDATE run at three in the morning during an incident.

#What does not belong there

Constraints are not a place for business rules that legitimately change, or for anything needing a human-readable explanation of why something was refused. A database error is a poor user experience, and it arrives too late to be helpful.

The workable division: the database refuses states that must never exist; the application refuses states that are currently not allowed, with an explanation. The two overlap, and that redundancy is the point. Application validation is the polite front door; the constraint is the lock.

#The portability objection

Teams sometimes avoid constraints to stay database-agnostic. In practice, almost nobody changes database engine, and the ones who do rewrite far more than their constraints.

Meanwhile the cost of the abstraction is paid every day, by every engineer, in the form of a schema that documents nothing.

#Where to start on an existing system

Do not attempt the whole schema. Take the tables that have caused an incident, and ask what state the data was in when the incident happened. Then write the constraint that would have made that state impossible.

Expect the first one you try to fail because the data is already invalid. That is not a reason to stop — it is the finding.