✅CHECK

Row-level laws beyond types

A CHECK constraint is a boolean expression evaluated for each row on insert/update (and sometimes on relevant column changes). It must evaluate to TRUE or UNKNOWN (NULL result): FALSE aborts the statement. Use CHECK for ranges, enum-like sets, and cross-column rules that are awkward to model only with types.

SQL

Range + cross-column rule

CREATE TABLE core.measurements (
  measurement_id BIGSERIAL PRIMARY KEY,
  read_low DOUBLE PRECISION NOT NULL,
  read_high DOUBLE PRECISION NOT NULL,
  CHECK (read_low <= read_high)
);

✓Illustrative result set

status
CREATE TABLE

SQL

Violation

INSERT INTO core.measurements (read_low, read_high) VALUES (10, 3);

✓Illustrative result set

error
ERROR: new row violates check constraint

🪜Tightening CHECK on dirty legacy data

PostgreSQL: NOT VALID → validate → VALIDATE CONSTRAINT pattern.

Add the constraint as NOT VALID so existing rows are not scanned immediately; backfill bad rows; then ALTER TABLE … VALIDATE CONSTRAINT under a controlled window. Other engines may need shadow columns or batch sweeps, portability matters for multi-DB products.

CHECK (status IN ('A','B','C')) without a migration story

Product adds status D; deploys fail everywhere until someone remembers the CHECK text buried in DDL from 2019.

FK to a status lookup table or versioned CHECK migrations

Prefer small reference tables with FK for evolving taxonomies; if you keep CHECK lists, treat changes like API versioning with explicit migration files.

Subqueries

Many engines disallow subqueries inside CHECK, use triggers, materialized columns, or generated stored columns for cross-table rules, knowing the operational cost.

Volatile functions

CHECK with NOW() or random values is either forbidden or meaningless, keep predicates stable per row revision.

⚡ Advanced / dialect note

Do not rely on short-circuit evaluation inside CHECK across engines for division safety, use NULLIF in expressions or split rules into multiple constraints with clear names.

Naming

Name constraints measurements_read_order_chk instead of system defaults, error messages in logs and ORM traces become self-explanatory during incidents.

TL;DR

A CHECK constraint enforces a Boolean predicate on every row insert/update in that table, violations abort the statement. It is ideal for intra-row rules (ranges, enums, format) but not for arbitrary cross-table logic (many engines forbid or discourage subqueries in CHECK); use foreign keys, triggers, or application validation for those. Keep predicates stable (avoid volatile functions like NOW() in CHECK unless your engine documents semantics).