CHECK Constraint

The catch-all constraint — any boolean expression you can write in SQL. Range checks, multi-column invariants, allowed-value lists, and NULL behaviour all explained, plus the NOT VALID escape hatch for huge tables.

The other constraints answer specific questions: "is this NULL?", "is this duplicated?", "does this exist over there?". A CHECK constraint lets you express any boolean rule you can write in SQL — and the database will reject every insert or update that doesn't satisfy it.

CHECK is the catch-all for the rules that don't fit into the other constraint types: salary > 0, end_date >= start_date, status IN ('open', 'closed'), email LIKE '%@%', and so on.

CREATE TABLE managers (
    manager_id  INT,
    first_name  VARCHAR(20),
    salary      NUMERIC CHECK (salary > 5000)
);

-- Below the floor — rejected
INSERT INTO managers VALUES (1, 'Alex', 4000);
-- ERROR: new row violates check constraint "managers_salary_check"

-- Passes the check
INSERT INTO managers VALUES (1, 'Alex', 10000);

To express "max_salary must be at least min_salary" you need a constraint that sees both columns. Put it at table level.

CREATE TABLE jobs (
    job_id      SERIAL PRIMARY KEY,
    title       VARCHAR(50) NOT NULL,
    min_salary  NUMERIC(8, 2),
    max_salary  NUMERIC(8, 2),
    CONSTRAINT salary_range_chk CHECK (max_salary >= min_salary)
);

-- Inverted range — rejected
INSERT INTO jobs (title, min_salary, max_salary) VALUES ('CEO', 100000, 50000);
-- ERROR: new row violates check constraint "salary_range_chk"

CREATE TABLE orders (
    order_id  SERIAL PRIMARY KEY,
    status    VARCHAR(20) NOT NULL
              CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

This is the classic "enum-by-CHECK" pattern — quick to set up, no extra type to manage. PostgreSQL also has actual ENUM types if you'd rather encode the allowed values in the type system.

CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    discount    NUMERIC(5, 2) DEFAULT 0,
    CONSTRAINT products_chk CHECK (
        price > 0
        AND discount >= 0
        AND discount <= price
    )
);

One named constraint, three rules. Combining with AND keeps related invariants together; using separate constraints lets you drop or refine them independently.

-- Add a check on an existing table (validates current rows)
ALTER TABLE products
    ADD CONSTRAINT price_positive CHECK (price > 0);

-- Drop it
ALTER TABLE products
    DROP CONSTRAINT price_positive;

Adding a CHECK validates every existing row. If any of them would fail, the constraint isn't created until the data is fixed. For huge tables, you can add the constraint as NOT VALID (skips the validation pass) and validate it later on a separate command.

-- Add fast, validate later
ALTER TABLE products
    ADD CONSTRAINT price_positive CHECK (price > 0) NOT VALID;

-- Run the scan when you're ready
ALTER TABLE products VALIDATE CONSTRAINT price_positive;

If any column referenced in the CHECK is NULL, the result of most boolean expressions is NULL — which CHECK treats as "passes." That's almost always the right choice (it lets nullable columns coexist with CHECKs), but it does mean a CHECK won't prevent NULL on its own.

CREATE TABLE t (n INT CHECK (n > 0));

INSERT INTO t VALUES (5);     -- ok
INSERT INTO t VALUES (-3);    -- rejected
INSERT INTO t VALUES (NULL);  -- accepted! NULL > 0 is NULL, not false

Combine with NOT NULL if you need both rules at once.

• It cannot reference other tables — that's what FOREIGN KEY is for.
• It cannot use subqueries.
• It cannot call non-immutable functions (e.g. NOW()) — the expression must be deterministic.
• For "no two rows have overlapping ranges" or similar cross-row rules, use an EXCLUSION constraint.

• CHECK enforces any boolean expression on each row.
• Column-level for single-column rules; table-level when multiple columns are involved.
• NULL values pass CHECKs by default — pair with NOT NULL when you need both.
• Use ADD CONSTRAINT … NOT VALID for a fast add on huge tables, then VALIDATE CONSTRAINT when ready.
• CHECK can't query other tables or use subqueries — use FOREIGN KEY or EXCLUSION for those cases.