CHECK Constraints - Last Line of Defense

Day 10 taught ACID. Day 13 taught “put the truth in the schema.” Day 15 taught FK integrity. Today: CHECK — predicates the engine enforces on every write, even when the app forgets.

Your Node service validates qty > 0. Then a one-off script, a buggy admin tool, or a second microservice writes qty = -5. Without a database rule, the illegal row is permanent. CHECK constraints move that rule into PostgreSQL so every writer — pool connections (Day 8), batch jobs, replicas of the same schema (Day 23) — hits the same wall.

Worked example: the app said no, the admin said yes

Product rule: order quantity must be positive; status must be one of a known set. The API enforces it in TypeScript:

// app/orders.ts — happy path only
if (qty <= 0) throw new BadRequest("qty must be > 0");
if (!STATUSES.has(status)) throw new BadRequest("unknown status");
await db.query(
  "INSERT INTO orders(id, qty, status) VALUES ($1, $2, $3)",
  [id, qty, status]
);

That path is fine for the React form. It is optional for everyone else. Ops runs a “fix inventory” script against the same table:

-- scripts/fix-inventory.sql  (runs as a privileged role)
INSERT INTO orders(id, qty, status) VALUES (9001, -5, 'ghost');
-- succeeds if the table has no CHECK
-- inventory report now shows -5 units for a status nobody defined

Two writers, one schema. The app’s validation never ran. The illegal row is durable. The fix is not “tell ops to be careful” — it is a constraint the engine evaluates on every INSERT/UPDATE:

CREATE TABLE orders (
  id     int PRIMARY KEY,
  qty    int NOT NULL,
  status text NOT NULL,
  CONSTRAINT orders_qty_positive
    CHECK (qty > 0),
  CONSTRAINT orders_status_known
    CHECK (status IN ('pending','paid','shipped','cancelled'))
);

INSERT INTO orders(id, qty, status) VALUES (9001, -5, 'ghost');
-- ERROR:  new row for relation "orders" violates check constraint
--         "orders_qty_positive"
-- DETAIL:  Failing row contains (9001, -5, ghost).

Same error for status = ‘ghost’ even when qty is legal. The statement fails with check_violation; the row is not stored. Day 10 atomicity: illegal domain state never becomes committed truth for that statement.

The problem: app-only validation is optional

Without CHECK the table is just columns:

CREATE TABLE orders (
  id     int PRIMARY KEY,
  qty    int NOT NULL,
  status text NOT NULL
);

Nothing stops this:

INSERT INTO orders(id, qty, status) VALUES (1, -5, 'ghost');
-- succeeds without CHECK. Your inventory and reports are now fiction.

FK (Day 15) stops “customer that doesn’t exist.” CHECK stops “quantity that can’t exist” and “status that isn’t in the domain.” App validation is still useful for fast UX errors. CHECK is the last line of defense when a second writer forgets the rule.

The mechanism: predicates on every write

On INSERT/UPDATE, PostgreSQL evaluates each CHECK against the candidate row. Failure → error check_violation, row not stored. Commit never sees the illegal state (the statement fails; the transaction can roll back or continue depending on how you handle errors — Day 10).

CHECKs can be column-ish or cross-column on the same row:

-- same-row cross-column rule is fine
CONSTRAINT orders_window_ok CHECK (end_at > start_at)

-- boolean expression, not a query against other tables
CONSTRAINT orders_discount_ok CHECK (discount_pct BETWEEN 0 AND 100)

Constraint toolbox: NOT NULL vs CHECK vs FK vs UNIQUE vs EXCLUDE

Each tool answers a different integrity question. Mixing them is how you get “CHECK that looks like an FK” or “UNIQUE that should have been EXCLUDE.”

Tool	Sees	Typical job	Example
NOT NULL	One column	Value must be present	qty int NOT NULL
CHECK	One row	Domain / shape / range	CHECK (qty > 0)
UNIQUE	Row pairs (=)	No duplicate key	UNIQUE (email)
FOREIGN KEY	Reference	Parent row exists (Day 15)	REFERENCES customers(id)
EXCLUDE	Row pairs (ops)	No conflict / overlap (Day 31)	EXCLUDE (room WITH =, during WITH &&)

Use CHECK when the rule is about this row’s meaning. Use FK when the rule is about reference to another entity. Use UNIQUE when equality collision is the only forbidden relation. Use EXCLUDE (Day 31) when the forbidden relation is richer than equality — especially time-range overlaps.

NOT VALID / VALIDATE: ship the rule without freezing the deploy

Legacy table already has dirty data. A full CHECK scan on ADD CONSTRAINT fails the migration and can take a long exclusive-ish scan on a huge table. PostgreSQL’s two-step path:

-- Step 1: attach the rule for NEW writes only (no full table scan)
ALTER TABLE orders
  ADD CONSTRAINT orders_qty_positive CHECK (qty > 0) NOT VALID;
-- existing rows not scanned; NEW writes must satisfy CHECK

-- Step 2: clean legacy rows in batches (app backfill, one-off SQL)
UPDATE orders SET qty = 1 WHERE qty <= 0;  -- or delete / quarantine

-- Step 3: prove the table; fails if any row still violates
ALTER TABLE orders VALIDATE CONSTRAINT orders_qty_positive;
-- scans table under a lighter lock than a cold ADD on some paths;
-- still work — schedule it for large tables

When to use this on large tables: any production table where (a) you cannot guarantee historical cleanliness, or (b) a full validation during the deploy window is too long / too locking. NOT VALID lets you stop the bleeding on new writes in the same release; validation becomes a planned maintenance step after cleanup. In our lab: legacy rows with qty = -5 stayed after NOT VALID; a new negative insert was rejected; VALIDATE CONSTRAINT failed until cleanup. That is the intended migration path — not a bug.

Note: NOT NULL is different historically (older Postgres could not NOT VALID a NOT NULL the same way). Prefer the documented CHECK path when the domain rule is a predicate, not mere presence.

What CHECK cannot do

CHECK is powerful and intentionally limited. Know the walls so you pick the right tool:

• Multi-row rules. A CHECK sees only the row being written. It cannot say “no other booking overlaps this one.” That is Day 31 EXCLUDE (or a carefully written trigger).

• Temporal overlaps across rows. CHECK (end_at > start_at) keeps one interval sane. Overlap between two intervals needs ranges + EXCLUDE (or an exclusion-friendly unique design).

• Cross-table predicates without help. Classic CHECK cannot query another table as a subquery in the general case you want for integrity. “Customer must be active” is usually an FK + status on the parent, an application rule, or a trigger — not a portable CHECK. Prefer FK (Day 15) for reference; prefer EXCLUDE for peer conflicts.

• Deferral of domain shape. CHECK is statement-time for the row. Multi-statement “break then fix” patterns are Day 34 deferred constraints (mostly FKs). Do not expect CHECK to wait until COMMIT while you temporarily write nonsense.

Rule of thumb: if the invariant mentions another row or another table’s existence, CHECK is probably the wrong tool.

Benchmark — integrity first, latency second

PostgreSQL 18.4 (Podman). Correctness is the headline; latency is the tax check. The standalone benchmark write-up is not included in this repository.

Scenario	Result
Bad inserts without CHECK	2 bad rows landed
Same inserts with CHECK	0 rows stored (rejected)
NOT VALID on legacy dirty table	existing 2 rows kept; new bad insert rejected; VALIDATE fails until cleanup
Valid bulk INSERT 2000 rows (30-run median)	No CHECK 2148.785 ms · With CHECK 2119.343 ms · 0.986× (−1.37%)
500 intentional CHECK failures (30-run median)	3266.116 ms

What breaks? — Anti-patterns

1. Trusting only the UI / one API path. Admin scripts, ETL, and second services skip your Zod schema. Fix: CHECK (and FK) on the system of record.

2. Encoding business rules only in enums in application code. A raw SQL client ignores them. Fix: mirror critical domains in CHECK or a real ENUM type + migrations.

3. Adding CHECK without a plan for legacy bad data. Migration fails mid-deploy on a million-row table. Fix: NOT VALID → clean in batches → VALIDATE CONSTRAINT.

4. Using CHECK for referential integrity. “Customer exists” is an FK problem, not a boolean on this row. Fix: FOREIGN KEY (Day 15); index the FK for joins.

5. Using CHECK for multi-row / overlap rules. Double-booking and “no two open invoices for the same account” need peers. Fix: EXCLUDE (Day 31) or UNIQUE / partial unique where equality fits.

6. Expecting CHECK to wait until COMMIT while you stage broken intermediate rows. That is a timing question, not a domain question. Fix: Day 34 deferred FKs for temporary reference gaps — keep CHECK for real domain truth.

How it connects

• Day 10 (ACID): a failed CHECK aborts the statement — illegal state never becomes committed truth.
• Day 13 (normalization): schema carries meaning; CHECK is meaning as an executable rule, not only a comment in a PRD.
• Day 15 (joins / FK): FK = reference integrity; CHECK = domain integrity on the row. Different jobs, often both on the same table.
• Day 8 (pooling): many app instances, one DB rule — constraints scale with writers without coordinating app deploys.
• Day 27 (WAL): rejected inserts don’t become durable commits; good constraints reduce garbage in the log of record.
• Day 30 (idempotency keys): retries must not double-charge; CHECK must not let a “successful” retry store nonsense domain values either — both are “exactly once / only legal effects.”
• Day 31 (exclusion constraints): when the invariant is about pairs of rows (overlaps, mutual exclusion), graduate from CHECK to EXCLUDE.
• Day 34 (deferred constraints): when the issue is when a reference is checked inside a multi-statement transaction, use DEFERRABLE FKs — not a weaker CHECK.

Transfer questions

1. You must enforce discount_pct between 0 and 100 and end_date >= start_date. Which belongs in CHECK, and why not only in the React form?
2. A migration adds CHECK (qty > 0) and fails on deploy because 40k legacy rows are negative. How do NOT VALID and VALIDATE CONSTRAINT change the rollout sequence on a large table?
3. Day 15 said PostgreSQL does not auto-index FKs. Does a CHECK on status create an index? When would you still add one?
4. Two concurrent “is the room free?” checks both pass in the app, then both insert overlapping bookings. Why can’t CHECK stop that, and what does Day 31 add?
5. An ETL loads children before parents in one transaction. Is CHECK the tool that should wait until COMMIT? What lesson covers the real answer?

What you should be able to do

• Write a CHECK for a positive quantity and a closed status set.
• Walk the worked example: app validates, admin script inserts -5, CHECK blocks with check_violation.
• Explain why app-only validation is not enough under multiple writers.
• Use NOT VALID / clean / VALIDATE CONSTRAINT as a safe migration pattern on large tables.
• Contrast NOT NULL, CHECK, UNIQUE, FK, and EXCLUDE without mixing their jobs.
• Name what CHECK cannot do (multi-row, temporal peer overlaps, cross-table existence) and point to Days 31 / 15 / 34.
• Report integrity results honestly: 2 bad rows → 0; bulk ≈ 0.986× noise — without inventing a scary latency tax.

Your teach step

Close this lesson. From memory: explain like I’m 10 (the database refuses nonsense), then a 60-second LinkedIn version with the worked example (app OK, admin -5 blocked), the 2 bad rows vs 0 rows result, and the honest “latency ≈ noise” note. Post it, paste the link.

Questions? Ask the agent — exclusion constraints (Day 31), deferred FKs (Day 34), partial unique indexes as “conditional uniqueness,” or domain types are fair game.