Your application validates that a quantity is positive before inserting. That holds for every write going through the application, which is most of them.
It does not hold for the data migration that ran last month, the maintenance script someone wrote, the bulk import from a partner, or the manual update executed during an incident at two in the morning. Each of those wrote directly, and none of them knew about your validation.
A check constraint holds for all of them, because the database enforces it.
What changed in 2026
- Non-blocking constraint addition became standard. Adding a constraint as not-valid and validating separately became the default approach on large tables.
- Constraints returned to favour. After a period of pushing validation entirely into applications, database-level integrity regained ground.
- Multi-writer reality became the argument. As more systems wrote to shared databases, application-only validation looked visibly insufficient.
- Generated column pairing spread. Combining computed columns with constraints on them enabled rules that were previously awkward.
What they can and cannot express
A check constraint is a boolean expression over columns of the same row.
| Rule |
Expressible? |
| Value is positive |
Yes |
| End date after start date |
Yes |
| Status is one of a set |
Yes |
| Email contains an @ |
Yes, crudely |
| Discount not more than the price |
Yes, same row |
| Total matches sum of line items in another table |
No |
| Only one active row per user |
No — use a partial unique index |
| Value is less than the current date |
No — not deterministic |
Two limitations do most of the work.
Same row only. No subqueries, no references to other tables. Cross-table rules need foreign keys, triggers, or application logic.
Deterministic only. The expression must produce the same result for the same row every time. Referencing the current time is prohibited, because a row valid at insert would become invalid later, and the database cannot maintain that.
For rules a check constraint cannot express, other tools apply: exclusion constraints for overlap rules, partial unique indexes for conditional uniqueness per partial indexes, and foreign keys for referential rules.
Adding one without locking
The operational concern. Adding a constraint normally requires the database to verify every existing row, which takes a lock for the duration — on a large table, an outage.
The two-step approach avoids it:
Add it as not-valid. The constraint is created and enforced for new and modified rows, without checking existing ones. This takes a brief lock rather than a long one.
Validate it separately. A subsequent validation step scans existing rows with a much weaker lock that does not block normal writes.
If validation finds violations, you have discovered a data quality problem — fix the rows, then validate again. That is a better outcome than the constraint failing to apply at all.
Order matters when adding a constraint to a table with existing bad data: clean first, or the constraint cannot be validated.
Naming and maintenance
Give constraints explicit names. Generated names are unreadable, appear verbatim in error messages users may see, and make migrations harder to write and review.
A name stating the rule — rather than the columns — makes a violation self-explanatory: an error naming a positive-quantity constraint tells you what happened without opening the schema.
Be deliberate about scope. Constraints encoding rules that change frequently are a poor fit, because changing a constraint is a schema migration while changing application logic is a deploy. Encode invariants that are genuinely stable properties of the data; leave changeable business policy in code.
Common mistakes
- Relying on application validation alone. Every other writer bypasses it.
- Adding constraints to large tables without the not-valid step. Long lock.
- Non-deterministic expressions. Rejected, and the reason is not always obvious.
- Generated names. Unreadable errors, awkward migrations.
- Encoding volatile business rules. Schema changes are slower than code changes.
- Trying to reference other tables. Not possible; use a different mechanism.
- Not cleaning data first. Validation fails and the constraint stays unvalidated.
FAQ
Do constraints slow down writes?
Marginally — an expression evaluated per write. Negligible compared with the cost of the write itself, and vastly cheaper than discovering bad data later.
Should I duplicate validation in the application?
Yes, for the user experience. The application should catch violations early with a helpful message; the constraint is the backstop that catches everything else.
How do I handle a constraint that must change?
Add the new one as not-valid, migrate the data, validate, then drop the old one — the same shape as any breaking change, per expand and contract migrations.
What about performance on bulk loads?
Constraints are checked per row. For very large loads, some workflows drop and re-add constraints, which requires confidence that the loaded data is clean — see COPY bulk loading.
Where to go next
For rules constraints cannot express, read exclusion constraints and partial indexes. For deferring constraint checks within a transaction, deferrable constraints.