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
CHECKcan use any condition, like>,BETWEENorIN.- PostgreSQL checks the rule on every insert and update.
- When you add a
CHECKto 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.