⛔NOT NULL

The column must always have a value

NOT NULL narrows a column’s domain: unknown is not an admissible state. That simplifies predicates (WHERE accession = … without three-valued surprises), helps cardinality estimates, and encodes product rules (“every sample has an accession”) at the same layer backups restore.

Adding NOT NULL to a populated table is a migration problem: you must decide what to do with existing NULLs before the catalog change succeeds.

SQL

Backfill, then tighten (PostgreSQL-style)

UPDATE core.samples SET accession = '(UNKNOWN)' WHERE accession IS NULL;

ALTER TABLE core.samples
ALTER COLUMN accession SET NOT NULL;

✓Illustrative result set

step
UPDATE n rows
ALTER COLUMN SET NOT NULL

SQL

Insert violating NOT NULL fails fast

INSERT INTO core.samples (sample_id, accession)
VALUES (90001, NULL);

✓Illustrative result set

error
ERROR: null value in column "accession" violates not-null constraint

ORM omits field → implicit NULL insert

Serialization defaults in JSON and ORM “optional” fields combine to NULL inserts that only fail in production under real payloads.

Server defaults + API validation + NOT NULL together

Align OpenAPI required fields with DB NOT NULL; add integration tests that post partial bodies and expect 400 + constraint errors.

🔗JOINs can reintroduce NULL

NOT NULL on base tables does not protect outer-joined projections.

A LEFT JOIN to an optional dimension still produces NULLs in the SELECT list, even when the fact table column is NOT NULL. Use COALESCE with documented semantics or inner joins when the dimension is truly mandatory for that report.

⚡ Advanced / dialect note

Prefer expand/contract migrations: add nullable column + backfill job + CHECK or NOT NULL in a later release, avoids multi-hour locks on terabyte tables during a single deploy window.

Documentation

For columns that remain nullable on purpose, document why (“instrument not yet assigned”) in the schema comment or ADR, future you should not tighten them blindly.

TL;DR

NOT NULL forbids storing unknown values in a column, inserts and updates must supply a concrete value (or a default). It documents required business facts at the database boundary and helps the optimizer reason about cardinality. Adding NOT NULL to existing wide tables requires a backfill + validate strategy so the constraint does not fail on legacy holes.