What does a CHECK constraint do in a relational table, and at what point does the database engine evaluate it?
answer
- Per-row predicate, evaluated at write
- Rejects only on FALSE; UNKNOWN passes
- INSERT + UPDATE-of-that-row; never DELETE
- No other rows, no subqueries, no now()
- Name it, or the error is useless
basics
~20 sA CHECK constraint is a boolean condition attached to a table. The engine tests it for every row a statement inserts or updates; if the condition comes out false, the statement fails with a constraint-violation error and the row is not stored.
solid answer
~50 sA CHECK constraint declares a predicate that every row must satisfy, enforced by the database itself. It is evaluated **per row, at write time**: on INSERT for the new row, and on UPDATE for the new version of each modified row. Rows the statement does not touch are not re-tested, and DELETE never triggers a CHECK. The outcome rule matters: the write is rejected only when the predicate evaluates to **false**. True passes, and so does **unknown**, which is what you get when a NULL feeds the expression. That is why `CHECK (price > 0)` still admits a row with a NULL price. A violation raises an error that aborts the statement; whether the surrounding transaction is left usable depends on the engine and on whether you wrapped the statement in a savepoint. Practically, always name the constraint (`CONSTRAINT ck_orders_qty_positive ...`) so the error identifies which rule fired instead of an auto-generated name.
code
sql · 15 linesCREATE TABLE order_line (
id bigint PRIMARY KEY,
quantity integer NOT NULL
CONSTRAINT ck_order_line_qty_positive CHECK (quantity > 0),
unit_price numeric(12,2) NOT NULL,
discount_pct numeric(5,2),
CONSTRAINT ck_order_line_discount_range
CHECK (discount_pct BETWEEN 0 AND 100)
);
-- rejected: quantity > 0 evaluates to FALSE
INSERT INTO order_line VALUES (1, 0, 10.00, NULL);
-- accepted: discount_pct is NULL, predicate is UNKNOWN, not FALSE
INSERT INTO order_line VALUES (2, 5, 10.00, NULL);go deeper
Be able to state it plainly: a boolean rule on a row, checked on INSERT and UPDATE, error if false. Give one concrete example such as a non-negative amount.
Add the three-valued-logic rule (only FALSE rejects), the fact that DELETE never fires it, and why the expression must be deterministic.
Talk about naming constraints so errors are actionable, how the violation interacts with the surrounding transaction, and what the optimizer can infer from a declared CHECK.
Frame CHECK as the cheapest available place to encode a stable domain invariant, and be explicit about which invariants it cannot express, so the team does not try to bend it into cross-row rules.
## 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.
- Does a CHECK constraint fire when you DELETE a row, or when you UPDATE a row that the constraint's columns are not part of?DELETE never evaluates CHECK constraints — a per-row rule cannot be broken by removing a row. An UPDATE does re-evaluate every CHECK on the table for each row it modifies, even if the statement did not touch the columns named in the predicate, because the engine validates the whole new row image. Rows not matched by the UPDATE's WHERE clause are untouched and untested.
- Why do engines refuse, or warn against, expressions like CHECK (created_at <= CURRENT_TIMESTAMP)?Because the predicate is not a property of the row, it is a property of the row plus the current clock. A row that passed at insert time keeps satisfying the rule only by luck, and any later revalidation — a restore, a table rewrite, re-enabling the constraint — can fail on data the system itself wrote. Constraints must be deterministic functions of the stored values so that 'valid' is a stable property of the data.
saying these in an interview costs you the question
- Believing a CHECK rejects NULLs — it accepts them, because the predicate is UNKNOWN, not FALSE
- Thinking the constraint is evaluated at COMMIT rather than during the statement
- Assuming a newly added CHECK proves the existing rows already comply
- Writing CURRENT_DATE / random / session settings into the predicate
- Expecting CHECK to fire on DELETE, or to re-test rows the UPDATE did not match