CHECK Constraint in PostgreSQL

A CHECK constraint lets you enforce custom data integrity rules at the database level. It evaluates a boolean expression whenever a row is inserted or updated — if the expression returns FALSE, the operation is rejected. This ensures invalid data never enters the table regardless of which application or script is writing to it.

Syntax

Inline column constraint (auto-named by PostgreSQL):

CREATE TABLE table_name (
  column_name data_type CHECK (boolean_expression)
);

Named table-level constraint (recommended):

CREATE TABLE table_name (
  column_name data_type,
  CONSTRAINT constraint_name CHECK (boolean_expression)
);

Practical Example

Create a products table enforcing that unit price is positive and that SKUs follow a prefix pattern:

CREATE TABLE categories (
  category_id   SERIAL PRIMARY KEY,
  category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  product_id    SERIAL PRIMARY KEY,
  sku           VARCHAR(20) NOT NULL UNIQUE,
  product_name  VARCHAR(200) NOT NULL,
  unit_price    NUMERIC(10, 2) NOT NULL,
  stock_qty     INT NOT NULL DEFAULT 0,
  category_id   INT REFERENCES categories (category_id),
  CONSTRAINT chk_unit_price_positive CHECK (unit_price > 0),
  CONSTRAINT chk_stock_non_negative  CHECK (stock_qty >= 0),
  CONSTRAINT chk_sku_prefix          CHECK (sku LIKE 'SKU-%')
);

Inserting a row with a negative price is rejected:

INSERT INTO products (sku, product_name, unit_price, stock_qty)
VALUES ('SKU-001', 'Wireless Headphones', -49.99, 100);

-- ERROR:  new row for relation "products" violates check constraint
-- "chk_unit_price_positive"

Inserting a row where the SKU prefix is wrong also fails:

INSERT INTO products (sku, product_name, unit_price, stock_qty)
VALUES ('WH-001', 'Wireless Headphones', 49.99, 100);

-- ERROR:  new row for relation "products" violates check constraint
-- "chk_sku_prefix"

A valid insert succeeds:

INSERT INTO products (sku, product_name, unit_price, stock_qty)
VALUES ('SKU-001', 'Wireless Headphones', 49.99, 100)
RETURNING *;

Add a cross-column CHECK to an existing table — for example, ensuring a sale price is always less than the regular price:

ALTER TABLE products
  ADD COLUMN sale_price NUMERIC(10, 2);

ALTER TABLE products
  ADD CONSTRAINT chk_sale_below_regular
    CHECK (sale_price IS NULL OR sale_price < unit_price);

Remove a constraint by name:

ALTER TABLE products DROP CONSTRAINT chk_sku_prefix;

Verify the remaining constraints:

SELECT
  conname AS constraint_name,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'products'::regclass
  AND contype = 'c';

Multi-Column and Function-Based Checks

CHECK constraints can reference any column in the same row and call deterministic functions:

CREATE TABLE events (
  event_id    SERIAL PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  starts_at   TIMESTAMPTZ NOT NULL,
  ends_at     TIMESTAMPTZ NOT NULL,
  min_age     INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_event_dates CHECK (ends_at > starts_at),
  CONSTRAINT chk_min_age     CHECK (min_age BETWEEN 0 AND 120),
  CONSTRAINT chk_title_len   CHECK (LENGTH(TRIM(title)) >= 3)
);

This blocks events where the end time is before or equal to the start time, invalid age ranges, and blank or whitespace-only titles — all enforced at the storage layer.

Testing with Vela

Before rolling out new CHECK constraints to a production table, use Vela’s database branching to create a copy-on-write clone of production data and test the new constraints against it. Run ALTER TABLE ... ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT on the branch to confirm how many existing rows violate the rule — without any impact on production. This workflow is especially useful when adding constraints to tables with years of historical data where edge cases may exist.

Production Tips

• Always name CHECK constraints explicitly with the CONSTRAINT clause. Auto-generated names like products_unit_price_check are difficult to reference in rollback migrations and error messages.
• When adding a CHECK to a large table, use NOT VALID to skip scanning existing rows: ALTER TABLE t ADD CONSTRAINT c CHECK (condition) NOT VALID; then separately: ALTER TABLE t VALIDATE CONSTRAINT c; — new inserts and updates are checked immediately; the validation step checks only pre-existing rows with a weaker lock.
• A NULL value in a CHECK expression evaluates to NULL (not FALSE), so the constraint passes for NULLs. Combine CHECK with NOT NULL when both conditions are required.
• Keep CHECK expressions lightweight. Avoid subqueries — PostgreSQL will reject them — and avoid calling volatile or user-defined functions that may behave differently across transactions.
• Use cross-column CHECK constraints (e.g., CHECK (end_date > start_date)) to encode business rules that span multiple columns. These can only be defined as table constraints, not inline column constraints.