Knobit of Data modelling · K-714 · Skill

Recognises nulls left to chance in a check predicate

What it means

A predicate that evaluates to `UNKNOWN` for nulls, where nobody decided whether a null passes. A `CHECK` passes `UNKNOWN`; a DQ row predicate fails it; a violation query written as `WHERE NOT (expr)` silently drops it. And `NOT IN` against a subquery that returns a null matches nothing.

How it is assessed

Flags the deficient case and passes the acceptable one. Deficient: Copied from the CHECK: `interest_rate_type_code IN ('FIX','VAR')` on an optional column — every current account with no rate type fails. Or `WHERE account_currency_code NOT IN (SELECT currency_code FROM ref_currency)` with one null code in the reference table: no violation ever found Acceptable: `interest_rate_type_code IS NULL OR interest_rate_type_code IN (...)` where null is allowed, and a separate completeness check where it is not; `NOT EXISTS` instead of `NOT IN`

Builds on

Explains null semantics and three-valued logicK-655 · Knowledge

Needed by

None