# Put invariants where concurrent database writes cannot break them > Use database constraints for durable invariants and application validation for context and feedback, with transactions covering rules across rows. Canonical URL: https://www.devobs.io/articles/database-constraints-versus-application-validation/ By: Amara Okafor Published: 2024-10-30T02:33:22.591Z Updated: 2026-09-05 Section: Architecture If a rule must remain true after concurrent writes, enforce it at the database boundary. Application validation still matters for clear messages and contextual rules, but it cannot be the sole guard for uniqueness, references, or other durable invariants shared by multiple writers. ## Classify the invariant by its race A uniqueness rule such as “one active membership per user and workspace” belongs in a unique constraint or a partial unique index. Two requests can both query, see no membership, and insert. Only a database object participating in both writes can arbitrate the race. References belong in foreign keys when the database owns both records. A preflight check that a project exists can become false before insertion. PostgreSQL’s [constraint documentation](https://www.postgresql.org/docs/current/ddl-constraints.html) explains that foreign keys maintain referential integrity and that unique constraints create unique indexes. Single-row facts such as positive quantity, valid date ordering, and required fields fit CHECK and NOT NULL. Name constraints so error handling can map a violation to a useful domain message. Remember that SQL null semantics matter: a check evaluating to unknown can pass, so pair checks with NOT NULL where absence is invalid. ## Treat cross-row rules as transactions Rules such as “reserved seats must not exceed capacity” or “an account balance may not cross its limit” require coordinated reads and writes. A plain check constraint is usually the wrong tool because the condition depends on other rows. PostgreSQL explicitly warns that its checks do not support references to other table data as a durable guarantee. Lock the aggregate row, update it atomically, use an exclusion constraint where the rule is an overlap, or run the transaction at an isolation level that detects the anomaly. The [PostgreSQL transaction isolation guide](https://www.postgresql.org/docs/current/transaction-iso.html) details phenomena allowed at each level and notes that serialization failures require transaction retries. For a seat reservation, an atomic statement can be clearer than a read-then-write sequence: UPDATE events SET reserved = reserved + 1 WHERE id = $1 AND reserved < capacity RETURNING reserved; No returned row means sold out. The condition and mutation share one serialization point. State transitions need similar treatment. An application may check that an order is pending before shipping, but the update should also include a condition on status. The affected-row count exposes a concurrent transition. For more complex workflows, store the allowed transition in a procedure or serialize on the entity row. ## Keep application validation for people Application validation handles syntax, field dependencies requiring request context, plan-specific limits, and explanations. It can report several problems at once before attempting a write. It also protects downstream services from malformed input. Run it early for usability, then still handle database violations. Constraint errors are expected outcomes under concurrency, not server bugs. Translate the named membership uniqueness violation into “This person is already a member.” Do not expose SQL or internal identifiers in the response. Some rules are intentionally soft: a title length recommendation, a warning about unusual allocation, or a limit that administrators can override with justification. These belong in application policy because the database should preserve the exceptional but valid state. ## Decide ownership explicitly For each rule, write down the invariant, its scope, all writers, the database mechanism, the user-facing validation, and retry behavior. Watch for secondary writers such as imports, migrations, scripts, and event consumers; they are why database enforcement pays off. Test the race, not just the message. Start two transactions that attempt the same unique value, overlapping reservation, or state transition. Assert that one succeeds and the other receives a handled domain result. Begin with the three most damaging invariants in your service. Add or validate their constraints, then make every application path translate the resulting violations consistently. ## Source references - -