Application level validation is a usability feature. It tells a user what is wrong before they submit. It is not a correctness guarantee, because it only applies on the paths that run it.
The paths that do not run it include the import script, the admin action somebody added in a hurry, the background job, the integration that writes directly, and the developer fixing a support issue at eleven at night with a SQL client.
A constraint in the database applies to all of them.
The constraints worth having
Not null on anything required. Obvious, routinely omitted, and the reason for a great deal of defensive code downstream.
Foreign keys. An order line pointing at a product that does not exist should be impossible rather than handled. Orphan rows are a category of bug that simply does not occur in a schema with proper references.
Unique indexes. On the values that must be unique: an email per tenant, a stock keeping unit, an external reference. This is also the mechanism that makes idempotency work.
Check constraints. A quantity that cannot be negative, a percentage between zero and one hundred, an end date after a start date. These are the rules everyone assumes are enforced somewhere.
Enumerated values. Either a database enum or a check constraint. A status column accepting arbitrary text will eventually contain three spellings of the same state.
Exclusion constraints stop double booking
This one deserves specific attention because the alternative is a race condition that is very hard to test for.
A booking system that checks availability and then inserts has a window between the check and the insert. Two requests can both pass the check and both insert, and you have two customers in one slot. It happens rarely enough to be dismissed as a mystery and often enough to matter.
PostgreSQL exclusion constraints solve it properly. A constraint over a resource identifier and a time range makes overlapping rows impossible at the database level. Two concurrent attempts cannot both succeed regardless of timing, because the second one violates a constraint.
This works when the constraint accurately represents the resource, relevant reservation states and interval boundaries. Shared capacity, multi resource bookings and cancellations need additional modelling rather than a copied constraint applied blindly.
Model money and stock as movements
Storing a balance as a number and editing it is the source of a whole class of problems. Two processes adjust it, a correction is applied twice, and by month end nobody trusts the figure and nobody can trace it.
The alternative is an append only ledger. Every receipt, sale, return, transfer, count and write off is an immutable row with a type, a quantity, a reason, an actor and a reference. The balance is derived.
That changes the character of a discrepancy entirely. Instead of an argument, you have a list of movements and one of them is wrong. Where performance requires it, a materialised balance is maintained alongside and can always be recomputed from the ledger.
Transactions around anything multi step
If an operation must either fully happen or not happen, it belongs in a transaction with the isolation level stated rather than assumed.
Creating an order and decrementing stock. Taking a booking hold and recording a local payment operation. Marking an event processed and applying its local effect. External processor calls cannot usually join the same database transaction; use durable work records, provider idempotency and reconciliation for that boundary.
What belongs in the application
Constraints are not a complete answer, and it is worth being clear about the division.
The database enforces invariants: things that must be true of the data regardless of how it got there. The application enforces process: who may do what, in what order, under which business conditions.
A simple check constraint is not the complete authorisation layer. Permissions can be enforced in application code and, where appropriate, database privileges or row security policies. Keep a clear distinction between the allowed numeric range and the identity permitted to change it.
The objection, answered
The usual argument against database constraints is that they make schema changes harder and push logic away from the application.
The first is partly true and is the point. A change that would break an invariant should be difficult, because it is a decision.
Constraints express durable data rules. Putting those rules next to the data prevents every writer from inventing a different definition of a valid record.
Add a constraint without concealing existing errors
Before applying a new rule to an established table, query for rows that already violate it. Classify the findings with someone who understands the operation. A duplicate external reference might be a repeated import, but it could also indicate that the reference is only unique within a vendor account. Deleting one row to make a migration pass can destroy the evidence needed to choose the correct key.
Define the repair separately from the constraint. Preserve related records, document any merged identifiers and verify totals after the repair. Rehearse the migration against representative data and account for locks or validation work that can affect active requests. A successful migration on an empty database proves little about a busy one.
Finally test the constraint through every supported write path, including imports and background jobs. Translate expected violations into useful application errors without exposing raw SQL or internal table names to users. An invariant that correctly rejects data still needs a recovery path: the operator must understand which value to correct and whether any other part of the operation was committed.