CHECK Constraint

What is a CHECK Constraint?

A CHECK constraint validates that values in a column satisfy a Boolean expression. If the expression evaluates to false, PostgreSQL rejects the INSERT or UPDATE.

CHECK constraints are powerful for enforcing business rules directly in the database, such as ensuring prices are positive or ages are within a valid range.

Syntax

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10,2) CHECK (price > 0)
);

Named constraint:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  age INT CONSTRAINT valid_age CHECK (age >= 0 AND age <= 150)
);

When to Use

• Ensuring prices are positive
• Validating that ages or quantities fall within a realistic range
• Enforcing that a status column only contains allowed values
• Multi-column checks (e.g., end_date > start_date)

Key Points

1. Boolean Expression — The condition must evaluate to TRUE or NULL for the row to be accepted.
2. Named Constraints — Use CONSTRAINT name CHECK (...) for clearer error messages.
3. Multi-Column — CHECK can reference multiple columns: CHECK (end_date > start_date).
4. NULLs Pass — A CHECK constraint evaluates to TRUE when the value is NULL, so combine with NOT NULL if needed.
5. ALTER TABLE — You can add CHECK constraints to existing tables.