What does a CHECK constraint do in a relational table, and at what point does the database engine evaluate it?

## What a CHECK constraint is A CHECK constraint is a declarative rule stored in the schema: a boolean expression over the columns of a single row, enforced by the database on every write. It sits alongside NOT NULL, keys and foreign keys as one of the integrity constraints the relational model offers, and its purpose is to make a class of bad data *impossible* rather than merely unlikely. Because the rule lives in the catalog, it applies to every writer: your application, a colleague's batch script, a hand-typed statement in a console, a data migration. Nothing can bypass it short of dropping or disabling the constraint. ## When it is evaluated The engine evaluates the expression **once per candidate row version, at the moment that version is written**: - **INSERT**: the new row is tested before it becomes visible. - **UPDATE**: the *post-update* image of each row the statement actually modified is tested. Rows that the WHERE clause did not select are never examined, even if they would fail the predicate. - **DELETE**: nothing is tested — removing a row cannot make the surviving rows violate a per-row rule. Evaluation is immediate by default: it happens as part of the statement, not at COMMIT. So the failing statement is the one that errors, which is exactly what you want for debugging — the error points at the culprit. A consequence people often miss: a CHECK constraint says nothing about rows that were already in the table before the constraint existed, unless the engine validated them when the constraint was created. Existing data and new data are separate concerns. ## True, false, unknown SQL predicates are three-valued: they can be TRUE, FALSE, or UNKNOWN. The rule for CHECK is that a row is rejected **only on FALSE**. UNKNOWN — the value produced whenever NULL participates in a comparison — is treated as acceptable. This is the opposite of a WHERE clause, which keeps only TRUE rows. So `CHECK (discount_pct BETWEEN 0 AND 100)` will happily accept `NULL`. If you want the column populated, that is a separate NOT NULL declaration, or you write the predicate to fail explicitly on NULL. ## Scope: one row, at that instant The expression may reference only columns of the row being written. It cannot look at other rows of the same table, cannot query other tables, and (in standard SQL as implemented by mainstream engines) cannot contain a subquery. That restriction is not arbitrary: to enforce a cross-row predicate correctly the engine would have to re-validate every affected row whenever *any* row changes, and would have to serialize concurrent writers to avoid two transactions each individually satisfying the rule while jointly breaking it. Per-row scope is what makes the constraint cheap and safe under concurrency. ## The expression must be deterministic A CHECK expression should depend on nothing but the row's own values. Engines forbid or strongly discourage non-deterministic inputs — the current time, session settings, sequence values, volatile functions — because the stored data would then satisfy the constraint at write time but violate it later. `CHECK (start_date >= CURRENT_DATE)` looks reasonable and is a trap: yesterday's perfectly legal row becomes a row that could never be re-inserted, and any operation that re-validates the table (a dump/restore, a table rewrite, a constraint revalidation) can suddenly fail. Rules about *now* belong in application logic or in a trigger, not in a CHECK. ## Placement and naming A CHECK can be written inline next to a column or as a separate table-level clause; the only real difference is that a table-level clause can reference several columns of the row. Either way, give it an explicit name. Auto-generated names differ between environments and change when a table is rebuilt, which breaks any code that maps the error back to a user-facing message. A stable name like `ck_orders_qty_positive` is both self-documenting in the schema and usable as a key in error handling. ## What you get for it Beyond correctness, CHECK constraints are documentation that cannot go stale, and query optimizers can use them: knowing that a column is always positive, or always one of four values, lets a planner discard impossible predicates or prune partitions. The cost is a small amount of CPU per written row — a boolean expression evaluated against values already in memory — which is normally negligible next to the I/O of the write itself. ## When to use one Good candidates: value ranges (`amount >= 0`), enumerated domains (`status IN ('NEW','PAID','SHIPPED')`), format sanity (a minimum length, a pattern), and relationships between columns of the same row (`ends_at > starts_at`). Poor candidates: anything requiring another row, another table, the current time, or a rule that product managers change monthly — those either belong to other constraint types or to code.