CHECK Constraint
CHECK constraints enforce custom rules on column values - ensuring price is positive, status is one of a fixed set, age is above 0, etc. They run at INSERT and UPDATE time and reject values that violate the condition.
CHECK Constraint Rules
- Defines a custom boolean condition a column's value must satisfy
- Evaluated on INSERT and UPDATE - violations cause an error
- Can reference multiple columns in the same table
- Cannot reference other tables (use triggers for that)
- CHECK with NULL: if the expression is NULL, the check passes (NULL means unknown)
- Name your constraints to get meaningful error messages
CHECK Examples
-- Price must be positive
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
-- Status must be a valid value
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status VARCHAR(50) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);
-- Named CHECK constraint (better error messages)
CREATE TABLE payments (
id SERIAL PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_positive_amount CHECK (amount > 0)
);
-- Multi-column CHECK: end date must be after start date
CREATE TABLE promotions (
id SERIAL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_date_order CHECK (end_date > start_date)
);
-- Violation:
INSERT INTO products (name, price, stock) VALUES ('Widget', -5.00, 10);
-- ERROR: new row for relation "products" violates check constraint "products_price_check"
-- Add CHECK to existing table
ALTER TABLE users ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');Quick Quiz
Tip
Tip
Practice CHECK Constraint in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Always use PRIMARY KEY + NOT NULL. FOREIGN KEY for referential integrity. CHECK for business rules.
Common Mistake
Warning
A common mistake with CHECK Constraint is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of CHECK Constraint from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- CHECK constraints enforce custom rules on column values - ensuring price is positive, status is one of a fixed set, age is above 0, etc.
- Defines a custom boolean condition a column's value must satisfy
- Evaluated on INSERT and UPDATE - violations cause an error
- Can reference multiple columns in the same table