UNIQUE, NOT NULL, and CHECK

Constraints are the cheapest data-quality layer you have

A CHECK or NOT NULL is enforced by the database before any dbt test, alert, or dashboard notices a problem. The bad row simply never lands. Three workhorses:

• NOT NULL: the column must always have a value.
• UNIQUE: no two rows share this value (or this combination of values, for a composite unique).
• CHECK (condition): every row must satisfy a boolean condition (an enum whitelist, a non-negative price, a valid date order).

Worked example

CREATE TABLE fact_order (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status      TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    order_date  TEXT,
    ship_date   TEXT,
    UNIQUE (customer_id, order_date),                 -- composite: one order per customer per day
    CHECK (ship_date IS NULL OR ship_date >= order_date)
);

Any insert with status = 'refunded', a negative total, or a ship date before the order date is rejected outright.

Anatomy of a column constraint:

status TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled'))
        │      │          │        │
      type  required   invariant  the whitelist the value must be in

Column-level constraints sit on one column; table-level constraints (UNIQUE (a, b), cross-column CHECK) go after the column list and can reference several columns.

In the warehouse this differs. SQLite enforces CHECK, NOT NULL, and UNIQUE reliably. Big analytical warehouses are looser: BigQuery has no CHECK/UNIQUE enforcement, and Snowflake enforces NOT NULL but treats UNIQUE/CHECK as informational. So in production these invariants are re-expressed as dbt / DQ tests (you'll build those in L4). Author them in your DDL anyway: they document intent and they are enforced on strict engines like Postgres.

Keep each CHECK to one clear invariant

A giant compound CHECK is unreadable and hard to debug when it fires. Common trap: a CHECK passes when its condition evaluates to NULL (three-valued logic). So a bare CHECK (ship_date >= order_date) already lets a missing ship date through: the NULL makes the condition unknown, not false, and it still rejects a wrong one. The ship_date IS NULL OR prefix in the worked example above adds no enforcement; it documents the intent for the next reader. If a ship date must always be present, that is a separate NOT NULL, not a longer CHECK. Watch the polarity flip: that same IS NULL OR prefix is load-bearing in a WHERE clause, where a NULL condition drops the row instead of letting it through.

Recap: constraints are the cheapest DQ layer. Use NOT NULL for required fields, UNIQUE (incl. composite) for identity/dedup, and CHECK for enums and invariants. Author them even where the warehouse won't enforce them.

Execution mode: you write a multi-statement DDL+DML script. It runs against a fresh in-memory SQLite DB, then hidden assertion queries check the constraints and row counts. Use INSERT OR IGNORE for rows you expect to be rejected. A constraint violation is then silently skipped instead of aborting your whole script.