CHECK Constraint

Validate column values against a boolean expression using CHECK constraints.

Overview

A CHECK constraint lets you define a custom rule using any boolean expression. If the expression evaluates to FALSE (not to NULL or TRUE), the INSERT or UPDATE is rejected. CHECK constraints move validation logic into the database ensuring that no application bug or direct database access can store invalid data. Good candidates for CHECK constraints: positive prices, valid status codes, date ranges (end_date > start_date), quantity > 0, age between 0 and 150. CHECK constraints can reference the column being defined or multiple columns in the same row.

Key Points

•CHECK (expression) rejected if expression evaluates to FALSE
•NULL is not FALSE CHECK passes if expression is NULL (useful for nullable columns)
•Can reference multiple columns in the same row (table-level constraint)
•Named constraint: CONSTRAINT chk_positive_price CHECK (price > 0)
•Add to existing table: ALTER TABLE t ADD CONSTRAINT name CHECK (expr)

Examples

Column and table-level CHECK constraints

CREATE TABLE products (
  id       SERIAL        PRIMARY KEY,
  name     TEXT          NOT NULL,
  price    NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  quantity INTEGER       CHECK (quantity >= 0),
  CONSTRAINT chk_name_length CHECK (LENGTH(name) >= 2)
);

-- Multi-column CHECK (table level)
CREATE TABLE events (
  id         SERIAL    PRIMARY KEY,
  start_date DATE      NOT NULL,
  end_date   DATE,
  CONSTRAINT chk_date_order CHECK (end_date IS NULL OR end_date >= start_date)
);

CHECK Constraint

Overview

Key Points

Examples

Exercises

Knowledge Check

A CHECK constraint expression evaluates to NULL. What happens to the INSERT?