Production engineering
Reveal answer and reasoning
SQL NULL represents a missing or unknown value within SQL's expression rules, which do not simply copy C# equality behavior. An equality comparison involving NULL normally yields unknown; a WHERE clause retains true, so amount = NULL does not select the missing values. IS NULL asks the missing-value question directly. The external fixture also separates row count from known-value count and tests two storage constraints independently.
These are real SQLite observations against a specified schema. They do not establish the behavior of every SQL engine, an application validation rule or all values permitted by SQLite's flexible type system.
Do not infer that CHECK(amount > 0) guarantees a present positive integer merely because ordinary positive integers pass it. In this SQLite fixture an unknown CHECK result is allowed, so NOT NULL supplies a separate requirement. SQLite column affinity also differs from a rigid static type declaration; stricter type requirements need their own schema and tests. The lesson does not exercise STRICT tables or arbitrary text input.
Likewise, COUNT(amount) excluding NULL does not mean those rows disappeared from the table. Conflating those contracts can silently change a report's denominator or permit a value the business rule meant to reject. The rollback-only insert does not test persistence after a successful NULL write; it deliberately leaves the original five-row fixture unchanged for independent reads.
What a strong answer covers
- WHERE retains true; NULL equality is unknown in the tested SQL contract.
- The fixed fixture has5 rows,2 NULL amounts and3 known amounts with sum6.
- CHECK-only NULL acceptance and NOT NULL rejection are independent actual SQLite checks.
Common pitfall
Assuming a positive-range CHECK also guarantees presence and a rigid integer type.
Follow-up questions
- What changes if every amount is NULL rather than zero?
- How would STRICT-table or type-affinity requirements be tested separately?
Self-review
Choose what to revisit. These marks are your own assessment and are separate from lesson completion.