Nulls and enforced invariants
What this concept is
A nullable value represents missing or unknown information, not a universal zero or empty string. SQL predicates can evaluate to unknown. Constraints and filtering use this logic differently, so each invariant needs a concrete rejected input and positive control.
Why it matters here
Guest orders deliberately have no customer ID. They must not appear in a customer report and must not make every customer disappear from an anti-join. Non-guest orders must refer to an existing customer; amount and status must always be present.
Concrete example
Suppose orders contains customer IDs 1 and NULL. For customer 2, 2 NOT IN (1,NULL) is unknown, so a WHERE filter rejects that row even though customer 2 has no order. NOT EXISTS with the correlated equality checks for actual matches and returns customer 2. Keep the guest order in the fixture specifically to expose this difference.
Common confusion / misconception
CHECK(cents >= 0) alone is not a NOT NULL declaration. Declare requiredness separately. Likewise, writing REFERENCES in SQLite is insufficient evidence that the current connection enforces it. The harness enables foreign_keys before transactions and reads the setting back. PostgreSQL has its own enforcement contract; do not carry SQLite setup commands into it.
How to use it
For this bounded SQLite exercise, enforce required integer cents with NOT NULL and a CHECK on typeof(cents) and nonnegativity. SQL type declarations and coercion differ across engines: this checks stored SQLite representation, not the original JSON input type. Use integer cents to avoid a floating-point rounding exercise; larger currency modelling is outside scope. Test missing amount, negative amount, fractional stored value, bad status, duplicate key and orphan customer separately.
Check yourself
Why does a nullable foreign key allow a guest order but reject customer 999? Would making customer_id NOT NULL solve the anti-join bug while preserving the intended domain?
Mini drill or application
Explain the policy for deleting a customer who has orders. The lab uses the default reference action and does not silently delete those orders. Add a test that distinguishes rejection from cascading deletion, then document whether the real domain should use either policy.
Read this only if stuck
Read keys and nulls in Database System Concepts, SQLite foreign keys, and PostgreSQL constraints. Use PostgreSQL subquery expressions to reconstruct NOT IN with a null.