Constraints are correctness, not decoration
A constraint is the one guarantee that survives every bug in every application that will ever talk to this database. It also has a cost, and the cost is on the write path.
12 min · runs against the shop dataset
Application code enforces rules until somebody writes a migration, a script, a console session or a second service. A constraint enforces them regardless, and it is the only mechanism in the system with that property. That is the argument for constraints, and it is a strong one.
The counter-argument is that every constraint is work on every write, and some of that work is more than people expect. A NOT NULL is free. A CHECK is an expression per row. A foreign key is a lookup per row, and without an index on the referencing column it is a lookup that scans.
This is the question a foreign key asks, once, per inserted row.
| orders_with_a_customerbigint |
|---|
| 90 |
1 row · 90 examined · 1 page read
The CHECK that lets nulls through
A CHECK passes when it is true or unknown, and only a definite false rejects the row. So a CHECK (price > 0) on a nullable column accepts a null price without complaint — the constraint doing exactly what it says and not at all what most people assume. If you meant to exclude nulls, say NOT NULL as well.
Surrogate against natural keys
A natural key is one the world already assigns: an email, an ISBN, a country code. A surrogate is one you invent. Natural keys read better and are correct until the world changes its mind — people change email addresses, countries merge, and the ISBN was reissued. A surrogate never changes, which is its whole point, and it costs you a join to get back to anything meaningful.
What a soft delete does to every index
Adding deleted_at and filtering it out everywhere sounds cheap. It is not: every index on the table now covers rows nobody will ever query, every query needs an extra predicate, and every unique constraint you had is now wrong — two rows can share an email if one of them is deleted, and the database no longer stops it.
The fix, when you need it, is a partial index: CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL. Smaller, faster, and it restores the guarantee the soft delete took away.