CHECK Constraint

The CHECK constraint lets you write your own rule for a column. Every row must pass the rule, or PostgreSQL will not save it.


Syntax

CREATE TABLE table_name (
  column_name datatype CHECK (condition)
);

Or for a table that already exists:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

The condition is written just like a WHERE condition.


Example 1: Price Must Be More Than 0

CREATE TABLE items (
  item_id INT PRIMARY KEY,
  name VARCHAR(50),
  price NUMERIC(8, 2) CHECK (price > 0)
);

INSERT INTO items VALUES (1, 'Pen', 1.50);

Output:

CREATE TABLE
INSERT 0 1

A pen for 1.50 passes the rule. Now try a price below zero:

INSERT INTO items VALUES (2, 'Notebook', -3);

Output:

ERROR:  new row for relation "items" violates check constraint "items_price_check"
DETAIL:  Failing row contains (2, Notebook, -3.00).

PostgreSQL named the constraint items_price_check.


Example 2: Add a CHECK to an Existing Table

You can name a constraint yourself with ADD CONSTRAINT:

ALTER TABLE employees
ADD CONSTRAINT age_limit CHECK (age BETWEEN 18 AND 70);

UPDATE employees SET age = 15 WHERE id = 1;

Output:

ALTER TABLE
ERROR:  new row for relation "employees" violates check constraint "age_limit"
DETAIL:  Failing row contains (1, Alice, 15, HR, female).

ALTER TABLE worked, because every employee already passes the rule. Then the update failed, because 15 is outside the range.


Key Points

  • CHECK can use any condition, like >, BETWEEN or IN.
  • PostgreSQL checks the rule on every insert and update.
  • When you add a CHECK to a table that has data, the rows that are already in it must pass the rule too.

In the exercise, add a CHECK rule to the employees table.