A table has the constraint CHECK (discount_pct < 100). A row is inserted with discount_pct absent (NULL) and the insert succeeds, yet a query filtering on discount_pct < 100 does not return that row. Why do a constraint and a filter treat the same condition differently?
answer
- constraint fails only on FALSE
- filter keeps only TRUE
- UNKNOWN accepted by CHECK, dropped by filter
- MATCH SIMPLE composite FK passes on any null
- CHECK is not a substitute for NOT NULL
basics
~20 sA check constraint rejects a row only when its condition evaluates to FALSE; UNKNOWN is accepted. A filter keeps a row only when the condition is TRUE; UNKNOWN is discarded. With an absent value the condition is UNKNOWN, so the constraint passes and the filter excludes.
solid answer
~50 sBoth evaluate the same three-valued expression, but they apply opposite defaults to the third outcome. A check constraint is defined as satisfied unless it evaluates to FALSE. Absent information cannot prove a violation, so UNKNOWN counts as 'not disproved' and the row is accepted. Filtering is the opposite: a row is retained only on TRUE, so both FALSE and UNKNOWN drop out. The same permissive rule appears elsewhere in integrity checking - a composite foreign key under the default MATCH SIMPLE semantics is satisfied whenever any referencing column is absent, regardless of whether a parent exists. The practical lesson is that a check constraint is not a substitute for NOT NULL. If absence must be forbidden, declare the column NOT NULL, or write the condition so absence is explicitly rejected. I have seen 'validated' columns full of nulls precisely because the team assumed the check would catch them.
code
sql · 9 lines-- accepts a row with no value: 'unknown < 100' is UNKNOWN, not FALSE
ALTER TABLE promo ADD CONSTRAINT ck_pct CHECK (discount_pct < 100);
-- rejects absence explicitly
ALTER TABLE promo ALTER COLUMN discount_pct SET NOT NULL;
-- conditional requirement: absence rejected only for percentage promos
ALTER TABLE promo ADD CONSTRAINT ck_pct_present
CHECK (discount_type <> 'PCT' OR discount_pct IS NOT NULL);go deeper
Remember the two rules: a constraint fails only on FALSE, a filter keeps only TRUE, so absent values pass the constraint and disappear from the filter.
Explain the reasoning (a violation must be provable) and that NOT NULL, not CHECK, is the tool for forbidding absence.
Extend it to composite foreign keys under MATCH SIMPLE, and to reports built from complementary filters that fail to sum to the total.
Set the standard: constraints validate presence-conditional shape, nullability is declared deliberately, and every constraint gets a negative test proving the absent case behaves as intended.
## Same expression, opposite default SQL evaluates predicates in three-valued logic, so any condition yields TRUE, FALSE or UNKNOWN. What differs between language constructs is the *disposition* of UNKNOWN. - **Integrity constraints** are violated only by FALSE. The standard states that a table check constraint is satisfied if the search condition is not false for any row. So absent information is accepted, on the reasoning that a constraint asserts something is provably wrong, and you cannot prove a violation from information you do not have. - **Row filtering** retains only TRUE. Anything not provably matching is dropped. Both defaults are defensible in isolation; the trouble is that engineers carry the intuition from one context into the other. The constraint reads like a validation rule ('discounts must be under 100'), so it is natural to expect it to reject a row with no discount at all. It does not, because 'unknown < 100' is unknown, and unknown is not false. ## Where else the permissive rule applies The same 'not false' disposition governs several other integrity mechanisms, which is worth knowing because they fail silently rather than loudly. **Composite foreign keys.** With the default MATCH SIMPLE semantics, a multi-column foreign key is satisfied whenever *any* of its referencing columns is absent - the engine does not look for a parent row at all. A row with a partially-filled composite key therefore passes referential integrity while pointing at nothing. MATCH FULL is the stricter alternative, requiring either all columns present or all absent. If a composite key must always be complete, mark the columns NOT NULL, which is the option that holds in every engine. **Column check constraints.** A single-column check behaves exactly like a table check: absence passes. ## Where the strict rule applies Filtering rows in a query keeps only TRUE. So does a search condition applied to grouped results. Uniqueness has its own, third rule, based on distinctness rather than equality. This lack of a single universal answer is exactly why 'how does NULL behave' has no one-line answer - the honest answer is 'it depends which subsystem is evaluating it, and constraints are the permissive one'. ## Consequences in practice The first consequence is a class of silent data defects: a column with a plausible-looking constraint that nonetheless fills with nulls, because every insert that omitted the value passed. Nobody gets an error, and the problem surfaces months later as reports that under-count. The second is a subtle interaction with filtering. Suppose the check asserts a percentage is under 100, so downstream queries assume every row satisfies it. A query filtering for rows where the percentage is at least 100 - looking for violations - returns nothing, apparently confirming the invariant, while the absent-value rows sit in neither result. Rows with absent values fall out of *both* a filter and its negation, so partitioning a table by a predicate and its complement does not necessarily cover the whole table. That is a good check to run mentally whenever two reports built from complementary filters fail to add up to the total. ## How to write it correctly If the value must always exist, declare NOT NULL. That is the strongest statement, it is enforced everywhere, and it removes the ambiguity instead of working around it. If the value is genuinely optional but must be valid when present, the existing check is already correct - absence passing is the behaviour you want, and it deserves a comment saying so, because the next reader will wonder. If you need a conditional requirement - 'if the discount type is percentage, the percentage must be present' - encode it in the check itself so the absent case is explicitly disallowed for those rows, remembering that the condition must evaluate to FALSE (not UNKNOWN) in the case you intend to reject. Testing this is straightforward and worth doing: insert the absent case deliberately and assert that it fails. Reviewing a constraint by reading it is unreliable, because the permissive rule is invisible in the text.
- Why did the standard choose to accept UNKNOWN in constraints instead of rejecting it?A constraint expresses that something is provably wrong. With absent information you cannot prove the assertion is violated, so rejecting would mean forbidding incomplete data outright - which is what NOT NULL is for. Keeping the two concerns separate lets a schema allow optional data while still validating it whenever it is present.
- What surprises people about a composite foreign key with a null in one column?Under the default MATCH SIMPLE semantics the constraint counts as satisfied if any referencing column is null, so no parent lookup happens and the row is accepted even though the partial key matches nothing. MATCH FULL requires all-or-nothing, but the portable fix is to declare the key columns NOT NULL so the partial case cannot arise.
saying these in an interview costs you the question
- Believing a CHECK constraint implicitly forbids absent values
- Saying UNKNOWN is treated as false everywhere in SQL
- Assuming a predicate and its negation together cover every row of the table
- Thinking a composite foreign key always verifies that a parent row exists