SQL CHECK Constraints: Enforcing Invariants in the Database

A CHECK constraint is a boolean condition the database evaluates on every row insert or update. If the expression returns FALSE, the operation fails. This pins a business rule into the schema instead of leaving it in application code that any other client can bypass.

Why keep invariants in the database

Your app may forget to validate. Production runs several services, and migrations or ad-hoc UPDATE statements route around your code entirely. A CHECK guarantees a bad row simply will not land, no matter where the query originates.

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  user_id    bigint NOT NULL,
  amount     numeric(12,2) NOT NULL CHECK (amount > 0),
  status     text NOT NULL CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
  created_at timestamptz NOT NULL DEFAULT now()
);

Two rules here: the amount is strictly positive, and the status must come from an allowed set. Inserting amount = 0 or status = 'refunded' raises an error.

Named constraints

An anonymous CHECK gets an auto-generated name like orders_amount_check. Name it explicitly instead: the name shows up in error messages and gives you a clean handle for DROP CONSTRAINT.

ALTER TABLE products
  ADD CONSTRAINT price_positive CHECK (price > 0);

-- Later, to change the rule:
ALTER TABLE products DROP CONSTRAINT price_positive;

• The name must be unique within the table.
• A descriptive name (price_positive, salary_nonneg) reads far better than an auto-generated one in logs.

Multi-column checks and ranges

A CHECK expression can reference several columns of the same row, which is ideal for ranges and cross-field consistency.

ALTER TABLE employees
  ADD CONSTRAINT salary_band CHECK (salary BETWEEN 30000 AND 500000),
  ADD CONSTRAINT not_self_manager CHECK (manager_id IS NULL OR manager_id <> id);

ALTER TABLE users
  ADD CONSTRAINT created_not_future CHECK (created_at <= now());

Key limit: a CHECK only sees the current row. It cannot read other rows or other tables (use foreign keys or triggers for that). In PostgreSQL the expression should also be deterministic; now() is technically allowed, but tying a check to "the current time" is fragile because it is only evaluated at write time.

NULL and three-valued logic

The big gotcha. If the expression evaluates to NULL (not TRUE or FALSE), the CHECK treats the row as valid. In other words, a CHECK only blocks values that are unambiguously false.

-- country may be NULL, and that PASSES the check:
ALTER TABLE users
  ADD CONSTRAINT country_iso CHECK (country IN ('US', 'GB', 'DE', 'BR'));

INSERT INTO users (id, email, country) VALUES (1, 'a@x.io', NULL); -- OK!

To forbid NULL, add a separate NOT NULL or fold the test into the condition:

ALTER TABLE users
  ADD CONSTRAINT country_required
  CHECK (country IS NOT NULL AND country IN ('US', 'GB', 'DE', 'BR'));

Adding on large tables: NOT VALID

ADD CONSTRAINT ... CHECK scans the entire table while holding a lock; on millions of rows that is a long stall. PostgreSQL lets you add the constraint as NOT VALID: it takes effect immediately for new and updated rows, but existing rows are not checked. You validate them later in a separate, lighter step.

ALTER TABLE orders
  ADD CONSTRAINT amount_positive CHECK (amount > 0) NOT VALID;

-- Validate existing rows without a long exclusive lock:
ALTER TABLE orders VALIDATE CONSTRAINT amount_positive;

Engine differences:

• MySQL supports CHECK from 8.0.16; older versions accepted the syntax but silently ignored it. There is no NOT VALID here.
• ClickHouse has CONSTRAINT ... CHECK, but it is evaluated on insert and optimized for analytics differently; do not lean on it as a strict OLTP invariant.

Bottom line: use CHECK for simple, row-local invariants, always name them, keep NULL semantics in mind, and reach for NOT VALID to roll out safely in production.