skip to content

A table has CONSTRAINT ck_price CHECK (price > 0) and an INSERT supplies NULL for price. Does the row go in? Explain the logic the engine applies.

level: middleimportance: must knowfreq 55%

answer

  1. NULL in → UNKNOWN out
  2. CHECK rejects only FALSE; WHERE keeps only TRUE
  3. NOT NULL is a separate constraint, use it
  4. FALSE AND UNKNOWN = FALSE, so partial info can still reject
  5. status IN (...) leaks a NULL fourth state

basics

~20 s

Yes, the row is accepted. NULL > 0 evaluates to UNKNOWN, not FALSE, and a CHECK constraint rejects a row only when its predicate is FALSE. To forbid the NULL you need NOT NULL on the column, or a predicate that handles NULL explicitly.

solid answer

~60 s

The insert succeeds. SQL comparisons are three-valued: any comparison with NULL yields **UNKNOWN**, and the CHECK rule is *reject only on FALSE*. UNKNOWN and TRUE both pass. So `CHECK (price > 0)` constrains the price *when there is one* and says nothing about its absence. This surprises people because a WHERE clause behaves the opposite way — it keeps only TRUE rows, so UNKNOWN drops out. Same expression, different disposition of the third value. Two fixes, and they mean different things: - Add `NOT NULL` to the column if the value is mandatory. This is the normal answer, and it is cheaper and clearer than folding nullability into the CHECK. - If the column is legitimately optional, leave it as is. The constraint already does its job for populated rows. If you deliberately want a nullable column but a predicate that fails on NULL, write it explicitly, for example `CHECK (price IS NOT NULL AND price > 0)` — but at that point you have just reimplemented NOT NULL with a worse error message. The same trap bites multi-column checks: `CHECK (ends_at > starts_at)` silently admits rows where either endpoint is NULL.

code

sql · 10 lines
sql
CREATE TABLE t (
  a integer,
  b integer,
  CONSTRAINT ck_t_a_lt_b CHECK (a < b)
);

INSERT INTO t VALUES (1, 2);        -- TRUE    -> stored
INSERT INTO t VALUES (2, 1);        -- FALSE   -> rejected
INSERT INTO t VALUES (1, NULL);     -- UNKNOWN -> stored
INSERT INTO t VALUES (NULL, NULL);  -- UNKNOWN -> stored

go deeper

for a junior

Know and state the outcome: the row is inserted, because NULL comparisons yield UNKNOWN and only FALSE rejects. Mention NOT NULL as the fix.

for a middle

Contrast CHECK with WHERE explicitly, and show that FALSE AND UNKNOWN still rejects — that detail proves you understand the propagation rules, not just the headline.

for a senior

Bring a production example of the trap (an enum CHECK on a nullable column) and the conditional-requirement pattern written as NOT p OR q with explicit IS NULL tests.

for a principal

Position it as a schema-review rule: nullability and value-domain are two separate decisions, and a review should force both to be stated rather than letting one imply the other.

## Three-valued logic in one paragraph SQL does not have two truth values, it has three: TRUE, FALSE and UNKNOWN. NULL means 'no value here', and any comparison involving NULL cannot be answered, so it yields UNKNOWN. `NULL > 0` is UNKNOWN. `NULL = NULL` is UNKNOWN. `NULL <> 5` is UNKNOWN. UNKNOWN then propagates through AND/OR with its own rules: `UNKNOWN AND FALSE` is FALSE, `UNKNOWN AND TRUE` is UNKNOWN, `UNKNOWN OR TRUE` is TRUE, `UNKNOWN OR FALSE` is UNKNOWN, and `NOT UNKNOWN` is UNKNOWN. ## The CHECK disposition rule Every place that consumes a predicate has to decide what to do with UNKNOWN, and different places decide differently: - A **WHERE** clause (and a JOIN condition, and HAVING) keeps a row only if the predicate is TRUE. UNKNOWN behaves like FALSE there. - A **CHECK constraint** rejects a row only if the predicate is FALSE. UNKNOWN behaves like TRUE there. So the very same expression is *restrictive* about NULL in a query and *permissive* about NULL in a constraint. This is not an accident or an engine quirk; it is the standard's design. A constraint is meant to assert 'this data is not known to be wrong'. Absence of information is not evidence of a violation, so it is allowed to pass, and the separate NOT NULL constraint exists precisely to say 'absence is itself forbidden'. ## Worked consequences `CHECK (price > 0)`: a NULL price is stored. The constraint means 'if a price is recorded, it is positive'. `CHECK (status IN ('NEW','PAID','SHIPPED'))`: a NULL status is stored, because `NULL IN (...)` is UNKNOWN. Enumerations enforced this way leak a fourth, unlabelled state unless the column is NOT NULL. `CHECK (ends_at > starts_at)`: rows with a NULL `ends_at` pass — often exactly what you want for an open-ended interval, and a bug if you assumed both endpoints were mandatory. `CHECK (discount_pct BETWEEN 0 AND 100)`: `BETWEEN` expands to two comparisons joined by AND; with a NULL operand both are UNKNOWN, the conjunction is UNKNOWN, and the row passes. `CHECK (a > 0 AND b > 0)` with `a = -1, b = NULL`: the first conjunct is FALSE, and FALSE AND UNKNOWN is FALSE, so the row is correctly rejected. Partial information can still be enough to prove a violation. This is worth knowing because it shows the rule is genuinely 'reject on FALSE', not 'reject when any operand is non-NULL and fails'. ## How to actually get the behaviour you want **The value is mandatory.** Declare `NOT NULL`. It is a distinct constraint with a distinct, clear error, it is checked without evaluating an expression, and optimizers understand it. Do not encode it inside the CHECK. **The value is optional but constrained when present.** Write the plain predicate and stop. `CHECK (price > 0)` on a nullable column is already correct and is the common, intended pattern. **Conditional requirement — the classic 'if type is X then field Y must be present'.** This is a legitimate multi-column CHECK, and here you must reason about NULL deliberately, for example `CHECK (kind <> 'SHIPPED' OR shipped_at IS NOT NULL)`. Note the shape: an implication written as `NOT p OR q`. With `kind` NOT NULL this evaluates to TRUE or FALSE and never leaks. **You want the CHECK itself to be NULL-proof.** Use IS NOT NULL explicitly, or a NULL-collapsing function such as COALESCE with a value that fails the test. Both work; both are usually a sign that NOT NULL was the right tool. ## Why this shows up in interviews It is a compact test of whether a candidate really internalised NULL semantics rather than memorising 'NULL means missing'. It also has a direct production consequence: a team ships `CHECK (amount >= 0)`, believes negative and missing amounts are both impossible, and later discovers a pile of NULL amounts written by a code path that never set the field. The constraint did exactly what it promised; the expectation was wrong. ## Related but distinct Other constraint types make their own choices about NULL, and they do not all match CHECK's. Uniqueness, in particular, has its own treatment of NULL rows in the standard and across engines, and primary keys forbid NULL outright. Do not generalise the CHECK rule to those — reason about each constraint type separately. ## Quick self-test Given `CHECK (a < b)` and the pairs `(1,2)`, `(2,1)`, `(1,NULL)`, `(NULL,NULL)`: the second is rejected, the other three are stored. If that list is instantly obvious to you, you have the rule.

  • The same predicate in a WHERE clause would filter the NULL row out. Why the difference?
    Because the two contexts dispose of UNKNOWN differently by design. A query returns rows it can prove match, so WHERE keeps only TRUE. A constraint blocks rows it can prove are wrong, so CHECK rejects only FALSE. Missing information is not proof of a violation, and NOT NULL exists as the separate way to forbid missing information.
  • How would you express 'shipped_at must be populated only when status is SHIPPED, and must be absent otherwise'?
    Write it as a table-level CHECK covering both directions, for example CHECK ((status = 'SHIPPED' AND shipped_at IS NOT NULL) OR (status <> 'SHIPPED' AND shipped_at IS NULL)). Because status is NOT NULL, every branch evaluates to TRUE or FALSE and no row slips through on UNKNOWN. Using IS NULL / IS NOT NULL rather than equality is what keeps the predicate two-valued.

A CHECK constraint is a bouncer who turns you away only when your ID clearly shows you are underage. No ID at all is not proof of anything, so you walk in. If you want everyone to show ID, that is a separate rule on the door — NOT NULL.

saying these in an interview costs you the question

  • Claiming the insert fails because NULL is not greater than zero
  • Saying CHECK and WHERE treat NULL the same way
  • Encoding mandatory-ness inside the CHECK instead of declaring NOT NULL
  • Assuming CHECK (status IN (...)) makes the column a closed enumeration on its own
  • Generalising 'UNKNOWN passes' to unique constraints and primary keys

context

resolving links…