Most application schemas leave domain validation entirely in application code. The application validates that an order quantity is positive, an email address has an at-sign, a percentage is between zero and one hundred, and a status field contains one of an expected set of values. The database accepts whatever the application sends. This works most of the time. The cases where it does not work are the cases where the validation matters most: ad-hoc backfill scripts, data migrations from other systems, manual SQL fixes during incidents, and edge cases in application code that escaped review. CHECK constraints close the gap with database-level enforcement that runs on every write regardless of source.
What CHECK constraints actually do
A CHECK constraint attaches an expression to a column or table that must evaluate to true or unknown for every row. The expression can reference any column in the row but cannot reference other rows or call non-immutable functions. Postgres evaluates the expression on every INSERT and UPDATE, rejecting writes that would violate it. The constraint is part of the schema, not the application, so every code path that writes to the table is subject to it.
The simplest form is a column-level CHECK like quantity INTEGER CHECK (quantity > 0) or percentage NUMERIC CHECK (percentage BETWEEN 0 AND 100). The table-level form allows multi-column predicates like CHECK (end_date IS NULL OR end_date >= start_date) which encodes the rule that a range with both endpoints must be non-empty. The naming form CONSTRAINT positive_quantity CHECK (quantity > 0) is the right default because the constraint name appears in error messages and migration scripts.
The everyday cases that earn CHECK constraints
Four cases come up repeatedly in B2B SaaS schemas. Numeric ranges including percentages and probabilities and ratios that have natural bounds. Status enums where application code expects one of a small set of values and any other value indicates a bug. Date relationships where end_date must be at or after start_date and effective_at must be in the past. String format constraints where an identifier follows a specific pattern enforceable in regex.
The numeric case is the cleanest. CHECK (percentage BETWEEN 0 AND 100) is unambiguous and survives every code path. The status enum case has two implementations: CHECK (status IN ('active', 'paused', 'archived')) or a separate enum type. The IN-list form is easier to evolve via ALTER TABLE because adding a new allowed value is a constraint modification rather than a type modification. The date relationship case prevents the off-by-one bugs that produce empty or inverted ranges from application timezone confusion.
What CHECK constraints cannot do
CHECK constraints cannot reference other rows in the same table or rows in other tables. This rules out uniqueness rules that depend on multi-row conditions, foreign-key-style references, and aggregate-based predicates like the sum of a column must equal a value in another row. The right tools for those cases are UNIQUE constraints, EXCLUDE constraints with btree_gist for non-overlap conditions, foreign keys, and triggers for the genuinely complex cases.
CHECK constraints also cannot call non-immutable functions. The expression must produce the same result for the same input every time, which rules out functions that read the current time, query other tables, or depend on session settings. Postgres enforces this restriction by rejecting constraint definitions that call functions marked STABLE or VOLATILE.
The migration story for existing tables
Adding a CHECK constraint to a populated table normally requires scanning every row to verify the constraint holds. The ALTER TABLE ADD CONSTRAINT statement takes an ACCESS EXCLUSIVE lock for the duration of the scan, which can be substantial for large tables. The mitigation is the NOT VALID clause: ALTER TABLE ADD CONSTRAINT name CHECK (expression) NOT VALID adds the constraint to the schema and enforces it for new writes but does not validate existing rows. The follow-up ALTER TABLE VALIDATE CONSTRAINT name validates the existing rows under a weaker SHARE UPDATE EXCLUSIVE lock that does not block reads or writes.
The two-step pattern is the right discipline for any production constraint addition. The first step ships immediately and starts enforcing for new data. The second step runs at a chosen time and validates the historical data, with the option to fix violations before validation succeeds. The result is the same as the all-at-once form but without the lock-related downtime.
Our use across the four products
Across DocuMint, CronPing, FlagBit, and WebhookVault, every numeric quota column has a CHECK constraint enforcing the non-negative-and-bounded property. Every status enum column has a CHECK against the allowed-value list. Every date-range pair has a CHECK enforcing end-at-or-after-start. The constraints have caught three real bugs across the four products since we added them, each from a manual backfill that would have produced silently broken state. The migration to add the constraints used the NOT VALID then VALIDATE CONSTRAINT pattern in every case, producing zero customer-visible downtime.
The deeper observation is that schema-level validation is one of those features that produces no visible value when it works and saves an incident when it catches a bug. Most schemas skip it because the application-level validation feels sufficient until it does not. The cost of adding CHECK constraints is small and the cost of debugging the bugs they prevent is large.
Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.