SQL 13 🛢️ CHECK Constraints
The previous chapter covered the three constraints that define simple rules for individual columns: NOT NULL, UNIQUE, and DEFAULT. This chapter covers the constraint that defines complex rules: CHECK. The CHECK constraint specifies a condition that the value in a column must satisfy. The condition can be as simple as price >= 0 or as complex as start_date <= end_date AND (status != 'cancelled' OR cancellation_date IS NOT NULL).
The LFCA and SQL chapters in this series have covered the relational model, the data types, and the CREATE TABLE statement. This chapter goes deeper into the CHECK constraint, which is the most flexible of the column-level constraints. It can validate a single column, multiple columns, or an entire row. It can use any expression that evaluates to a boolean. It is the tool that lets the database enforce the business rules that the application would otherwise have to enforce itself.
Key point: A CHECK constraint is a boolean expression. The database evaluates the expression on every insert and update. If the expression is true or null, the operation succeeds. If the expression is false, the operation fails. The rule is simple: a CHECK constraint passes when it is not violated. A null result is not a violation. This is why a CHECK constraint on a nullable column does not require a value — it only requires that any value present satisfies the condition.
Why CHECK constraints matter
The NOT NULL constraint requires a value. The UNIQUE constraint prevents duplicates. The CHECK constraint validates the value itself. It is the tool that enforces the domain of the column — the set of values that the column is allowed to hold.
The domain problem. An age column should hold a number between 0 and 130. Without a CHECK constraint, the column accepts any integer, including negative numbers and values that are absurdly large. The CHECK constraint age >= 0 AND age <= 130 rejects the invalid values. The domain of the column is enforced by the database.
The consistency problem. A status column should hold one of a small set of values: 'pending', 'shipped', 'delivered', or 'cancelled'. Without a CHECK constraint, the column accepts any string, including typos like 'shiped' or 'canceled'. The CHECK constraint status IN ('pending', 'shipped', 'delivered', 'cancelled') rejects the invalid values.
The cross-column problem. An events table has start_date and end_date. The end date should not be before the start date. A CHECK constraint that references both columns enforces the rule: end_date >= start_date. The constraint is declared at the table level because it references more than one column.
The conditional problem. A discounts table has a discount_type column and a discount_value column. If the type is 'percentage', the value should be between 0 and 100. If the type is 'fixed', the value should be non-negative. A CHECK constraint with a CASE expression enforces the conditional rule.
The trade-off. A CHECK constraint adds a check on every insert and update. The check is fast for simple expressions and slower for complex ones. The constraint also makes the schema more rigid. A change to the business rule requires an ALTER TABLE statement to drop and re-add the constraint. The rigidity is the price of consistency.
a. Column-Level CHECK Constraints
A column-level CHECK constraint is declared after the column’s data type. It references only that column.
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) CHECK (price >= 0),
stock INTEGER CHECK (stock >= 0),
discount DECIMAL(5, 2) CHECK (discount >= 0 AND discount <= 100)
);The price column must be non-negative. The stock column must be non-negative. The discount column must be between 0 and 100. Each constraint references only the column it is attached to.
The CHECK constraint can reference a function. The function must be deterministic — it must return the same result for the same input. A function that returns the current date is not deterministic and should not be used in a CHECK constraint.
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
event_name VARCHAR(100) NOT NULL,
event_date DATE CHECK (event_date >= '2026-01-01')
);The event_date must be on or after January 1, 2026. The constraint uses a literal value. The value is fixed. The constraint is deterministic.
CREATE TABLE measurements (
measurement_id SERIAL PRIMARY KEY,
value NUMERIC CHECK (value > 0)
);The value must be positive. The constraint rejects zero and negative numbers.
A column-level CHECK constraint can be named. The name is used when the constraint is dropped or when the violation is reported.
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) CONSTRAINT price_positive CHECK (price >= 0)
);The CONSTRAINT price_positive CHECK (price >= 0) clause names the constraint. The name appears in the error message when the constraint is violated.
b. Table-Level CHECK Constraints
A table-level CHECK constraint is declared after all the column definitions. It can reference any column in the table.
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
event_name VARCHAR(100) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT valid_dates CHECK (end_date >= start_date)
);The valid_dates constraint references both start_date and end_date. The end date must be on or after the start date. The constraint is declared at the table level because it references more than one column.
A table-level CHECK constraint can combine multiple conditions with AND, OR, and NOT.
CREATE TABLE bookings (
booking_id SERIAL PRIMARY KEY,
room_id INT NOT NULL,
guest_count INT NOT NULL,
check_in DATE NOT NULL,
check_out DATE NOT NULL,
status VARCHAR(20) NOT NULL,
cancelled_at DATE,
CONSTRAINT valid_booking CHECK (
check_out > check_in
AND guest_count > 0
AND guest_count <= 10
AND (status != 'cancelled' OR cancelled_at IS NOT NULL)
)
);The valid_booking constraint enforces four rules. The check-out date must be after the check-in date. The guest count must be positive and no more than 10. If the status is 'cancelled', the cancelled_at column must have a value.
A table-level CHECK constraint can use a CASE expression for conditional logic.
CREATE TABLE discounts (
discount_id SERIAL PRIMARY KEY,
discount_type VARCHAR(20) NOT NULL,
discount_value NUMERIC NOT NULL,
CONSTRAINT valid_discount CHECK (
CASE discount_type
WHEN 'percentage' THEN discount_value BETWEEN 0 AND 100
WHEN 'fixed' THEN discount_value >= 0
ELSE FALSE
END
)
);The valid_discount constraint evaluates the CASE expression. If the type is 'percentage', the value must be between 0 and 100. If the type is 'fixed', the value must be non-negative. If the type is anything else, the constraint fails.
c. Adding, Dropping, and Validating CHECK Constraints
A CHECK constraint can be added to an existing table with ALTER TABLE ... ADD CONSTRAINT. The database validates the existing data first. If any row violates the constraint, the statement fails.
ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price >= 0);The constraint is validated against the existing rows. If all rows satisfy the condition, the constraint is added. If any row violates it, the statement fails with an error that identifies the violating row.
A CHECK constraint can be dropped with ALTER TABLE ... DROP CONSTRAINT. The constraint is removed. The data is unchanged.
ALTER TABLE products
DROP CONSTRAINT price_positive;In PostgreSQL, a CHECK constraint can be marked as NOT VALID. This adds the constraint without validating the existing rows. The constraint is enforced on new inserts and updates, but the existing rows are not checked. The constraint can be validated later with ALTER TABLE ... VALIDATE CONSTRAINT.
-- Add without validating
ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price >= 0) NOT VALID;
-- Validate later
ALTER TABLE products
VALIDATE CONSTRAINT price_positive;The NOT VALID option is useful when adding a constraint to a large table. The validation step takes an ACCESS EXCLUSIVE lock, which blocks all reads and writes. By adding the constraint as NOT VALID first, the lock is held only briefly. The validation step can be run later during a maintenance window.
A CHECK constraint can be disabled and re-enabled. In PostgreSQL, the ALTER TABLE ... DISABLE TRIGGER ALL command disables all triggers and constraints. The ENABLE TRIGGER ALL command re-enables them. The constraint is not enforced while it is disabled.
-- Disable all triggers and constraints
ALTER TABLE products DISABLE TRIGGER ALL;
-- Re-enable
ALTER TABLE products ENABLE TRIGGER ALL;The disable option should be used with caution. The data can become invalid while the constraint is disabled. The constraint should be validated before it is re-enabled.
Complete Example Session
This session builds a table with multiple CHECK constraints and demonstrates their behavior.
-- ============================================
-- PART 1: THE TABLE WITH CHECK CONSTRAINTS
-- ============================================
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
age SMALLINT CHECK (age >= 18 AND age <= 65),
salary DECIMAL(10, 2) CHECK (salary >= 0),
hire_date DATE NOT NULL,
termination_date DATE,
CONSTRAINT valid_dates CHECK (
termination_date IS NULL OR termination_date >= hire_date
),
CONSTRAINT valid_email CHECK (email LIKE '%@%.%')
);
-- age: between 18 and 65
-- salary: non-negative
-- termination_date: null or after hire_date
-- email: contains @ and a dot after it
-- ============================================
-- PART 2: TEST THE AGE CONSTRAINT
-- ============================================
INSERT INTO employees (first_name, last_name, email, age, salary, hire_date)
VALUES ('Alice', 'Johnson', 'alice@example.com', 30, 50000, '2026-01-01');
-- Succeeds.
INSERT INTO employees (first_name, last_name, email, age, salary, hire_date)
VALUES ('Bob', 'Smith', 'bob@example.com', 17, 50000, '2026-01-01');
-- Error: new row for relation "employees" violates check constraint
-- "employees_age_check"
-- DETAIL: Failing row contains (2, Bob, Smith, bob@example.com, 17, 50000.00, 2026-01-01, null).
-- ============================================
-- PART 3: TEST THE SALARY CONSTRAINT
-- ============================================
INSERT INTO employees (first_name, last_name, email, age, salary, hire_date)
VALUES ('Carol', 'Williams', 'carol@example.com', 25, -1000, '2026-01-01');
-- Error: violates check constraint "employees_salary_check"
-- ============================================
-- PART 4: TEST THE VALID_DATES CONSTRAINT
-- ============================================
INSERT INTO employees (first_name, last_name, email, age, salary, hire_date, termination_date)
VALUES ('Dave', 'Brown', 'dave@example.com', 40, 60000, '2026-01-01', '2025-12-01');
-- Error: violates check constraint "valid_dates"
-- The termination date is before the hire date.
-- ============================================
-- PART 5: TEST THE VALID_EMAIL CONSTRAINT
-- ============================================
INSERT INTO employees (first_name, last_name, email, age, salary, hire_date)
VALUES ('Eve', 'Davis', 'invalid-email', 28, 55000, '2026-01-01');
-- Error: violates check constraint "valid_email"
-- ============================================
-- PART 6: THE NULL BEHAVIOR
-- ============================================
INSERT INTO employees (first_name, last_name, email, age, salary, hire_date)
VALUES ('Frank', 'Miller', 'frank@example.com', NULL, 45000, '2026-01-01');
-- Succeeds.
-- The age constraint evaluates to NULL, not FALSE.
-- A NULL result does not violate the constraint.
-- ============================================
-- PART 7: THE MULTI-COLUMN CHECK
-- ============================================
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
event_name VARCHAR(100) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
max_attendees INT NOT NULL,
CONSTRAINT valid_event CHECK (
end_date >= start_date
AND max_attendees > 0
)
);
-- ============================================
-- PART 8: THE CONDITIONAL CHECK
-- ============================================
CREATE TABLE discounts (
discount_id SERIAL PRIMARY KEY,
discount_type VARCHAR(20) NOT NULL,
discount_value NUMERIC NOT NULL,
CONSTRAINT valid_discount CHECK (
CASE discount_type
WHEN 'percentage' THEN discount_value BETWEEN 0 AND 100
WHEN 'fixed' THEN discount_value >= 0
ELSE FALSE
END
)
);
INSERT INTO discounts (discount_type, discount_value)
VALUES ('percentage', 150);
-- Error: violates check constraint "valid_discount"
INSERT INTO discounts (discount_type, discount_value)
VALUES ('unknown', 50);
-- Error: violates check constraint "valid_discount"
-- ============================================
-- PART 9: ADDING A CHECK CONSTRAINT
-- ============================================
ALTER TABLE employees
ADD CONSTRAINT valid_phone CHECK (phone IS NULL OR phone ~ '^\+?[0-9]{10,15}$');
-- The constraint is validated against the existing rows.
-- If any row has a phone that does not match, the statement fails.
-- ============================================
-- PART 10: THE CHECK CONSTRAINT SUMMARY
-- ============================================
-- CHECK: a boolean expression that must not be false
-- Column-level: references only the column
-- Table-level: references any column
-- NULL result: not a violation
-- FALSE result: a violation
-- NOT VALID: add without validating existing rows
-- VALIDATE CONSTRAINT: validate laterThe ten parts cover the table with CHECK constraints, testing the age constraint, testing the salary constraint, testing the valid dates constraint, testing the valid email constraint, the null behavior, the multi-column check, the conditional check, adding a check constraint, and the summary.
Quick Reference
The CHECK Constraint Syntax
| Form | Example |
|---|---|
| Column-level | price DECIMAL CHECK (price >= 0) |
| Named column-level | price DECIMAL CONSTRAINT price_positive CHECK (price >= 0) |
| Table-level | CONSTRAINT valid_dates CHECK (end_date >= start_date) |
| Conditional | CHECK (CASE type WHEN 'a' THEN ... END) |
The CHECK Operators
| Operator | Purpose |
|---|---|
>= | Greater than or equal |
<= | Less than or equal |
BETWEEN | Range |
IN | Set membership |
LIKE | Pattern matching |
IS NULL | Null check |
AND | Both conditions |
OR | Either condition |
NOT | Negation |
CASE | Conditional |
The CHECK Result
| Result | Behavior |
|---|---|
TRUE | The row is accepted |
FALSE | The row is rejected |
NULL | The row is accepted |
The ALTER TABLE Operations
| Operation | Syntax |
|---|---|
| Add constraint | ALTER TABLE t ADD CONSTRAINT name CHECK (condition) |
| Add NOT VALID | ALTER TABLE t ADD CONSTRAINT name CHECK (condition) NOT VALID |
| Validate | ALTER TABLE t VALIDATE CONSTRAINT name |
| Drop constraint | ALTER TABLE t DROP CONSTRAINT name |
Best Practices
✅ Do This:
-- Use CHECK for domain validation
age SMALLINT CHECK (age >= 18 AND age <= 65) -- ✅-- Use CHECK for cross-column rules
CONSTRAINT valid_dates CHECK (end_date >= start_date) -- ✅-- Use IN for enumerated values
status VARCHAR(20) CHECK (status IN ('pending', 'shipped', 'delivered')) -- ✅-- Name the constraints for clarity
CONSTRAINT price_positive CHECK (price >= 0) -- ✅-- Use NOT VALID for large tables
ALTER TABLE t ADD CONSTRAINT c CHECK (...) NOT VALID -- ✅❌ Don’t Do This:
-- Don't use non-deterministic functions
CHECK (created_at <= NOW()) -- NOW() changes -- ❌-- Don't use CHECK for referential integrity
CHECK (customer_id IN (SELECT id FROM customers)) -- ❌ use FK-- Don't use CHECK for complex business logic
-- Use the application or a trigger. -- ❌-- Don't expect CHECK to be enforced on existing data
-- Use VALIDATE CONSTRAINT to check. -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Constraint fails on insert | Value violates the condition | Fix the value |
| Adding constraint fails | Existing data violates | Fix the data first |
| NULL passes the check | NULL is not FALSE | Use NOT NULL if required |
| Slow validation | Large table | Use NOT VALID first |
| Non-deterministic error | Function changes | Use a deterministic expression |
Real-World Examples
1. Non-Negative
price DECIMAL CHECK (price >= 0)2. Range
age SMALLINT CHECK (age >= 18 AND age <= 65)3. Enumerated Values
status VARCHAR(20) CHECK (status IN ('pending', 'shipped', 'delivered'))4. Pattern Matching
email VARCHAR(100) CHECK (email LIKE '%@%.%')5. Cross-Column
CONSTRAINT valid_dates CHECK (end_date >= start_date)6. Conditional
CHECK (CASE type WHEN 'percentage' THEN value BETWEEN 0 AND 100 ELSE FALSE END)7. Null-Safe
CHECK (phone IS NULL OR phone ~ '^\+?[0-9]{10,15}$')8. Add NOT VALID
ALTER TABLE t ADD CONSTRAINT c CHECK (...) NOT VALID9. Validate
ALTER TABLE t VALIDATE CONSTRAINT c10. Drop
ALTER TABLE t DROP CONSTRAINT cVisual
The CHECK Constraint
┌──────────────────────────────────────────────┐
│ CHECK CONSTRAINT │
│ │
│ price DECIMAL(10, 2) CHECK (price >= 0) │
│ │
│ On every insert and update: │
│ ├─ Evaluate the expression │
│ ├─ TRUE → accept │
│ ├─ FALSE → reject │
│ └─ NULL → accept │
│ │
│ The constraint is the rule. │
│ The database enforces it. │
│ │
└──────────────────────────────────────────────┘The NULL Behavior
┌──────────────────────────────────────────────┐
│ NULL BEHAVIOR │
│ │
│ age SMALLINT CHECK (age >= 18) │
│ │
│ INSERT ... age = 25 → 25 >= 18 is TRUE │
│ └─ Accepted │
│ │
│ INSERT ... age = 15 → 15 >= 18 is FALSE │
│ └─ Rejected │
│ │
│ INSERT ... age = NULL → NULL >= 18 is NULL │
│ └─ Accepted │
│ │
│ A NULL result is not a violation. │
│ Use NOT NULL to require a value. │
│ │
└──────────────────────────────────────────────┘The Multi-Column Check
┌──────────────────────────────────────────────┐
│ MULTI-COLUMN CHECK │
│ │
│ CONSTRAINT valid_dates CHECK ( │
│ end_date >= start_date │
│ ) │
│ │
│ start_date │ end_date │ Valid? │
│ ───────────┼──────────┼──────── │
│ 2026-01-01 │ 2026-01-05│ YES │
│ 2026-01-05 │ 2026-01-01│ NO │
│ 2026-01-01 │ null │ NULL → YES │
│ │
│ The constraint references both columns. │
│ It is declared at the table level. │
│ │
└──────────────────────────────────────────────┘The NOT VALID Pattern
┌──────────────────────────────────────────────┐
│ NOT VALID PATTERN │
│ │
│ 1. Add constraint with NOT VALID: │
│ └─ Brief lock │
│ └─ Existing rows not checked │
│ └─ New inserts and updates are checked │
│ │
│ 2. Validate later: │
│ └─ ACCESS EXCLUSIVE lock │
│ └─ Existing rows are checked │
│ └─ Constraint becomes fully active │
│ │
│ Use for large tables to minimize locking. │
│ │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
CHECK constraint | Boolean expression that must not be false |
| Column-level | References only the column |
| Table-level | References any column |
| NULL result | Not a violation |
| FALSE result | A violation |
IN | Set membership |
LIKE | Pattern matching |
CASE | Conditional logic |
NOT VALID | Add without validating |
VALIDATE CONSTRAINT | Validate later |
Key takeaways:
- A
CHECKconstraint is a boolean expression. The database evaluates the expression on every insert and update. If the result isTRUEorNULL, the operation succeeds. If the result isFALSE, the operation fails . - A column-level
CHECKconstraint references only the column it is attached to. A table-levelCHECKconstraint can reference any column in the table. Multi-column rules require a table-level constraint . - A
NULLresult is not a violation. If the expression evaluates toNULL, the row is accepted. This is why aCHECKconstraint on a nullable column does not require a value. UseNOT NULLto require a value . - The
CHECKconstraint can use any boolean expression.AND,OR,NOT,BETWEEN,IN,LIKE, andCASEare all valid. The expression must be deterministic — the same input must always produce the same result . - Adding a
CHECKconstraint validates the existing data. If any row violates the constraint, the statement fails. The data must be corrected before the constraint can be added . - The
NOT VALIDoption adds the constraint without validating. The constraint is enforced on new inserts and updates, but the existing rows are not checked. The validation can be run later during a maintenance window . - The
CHECKconstraint is not a replacement for the foreign key. The foreign key enforces referential integrity between tables. TheCHECKconstraint enforces domain integrity within a table. Use the right constraint for the right purpose.
Remember: A CHECK constraint is a boolean expression that the database evaluates on every insert and update. It enforces the domain of the column, the consistency of cross-column rules, and the conditional logic that depends on multiple values. The NULL result is not a violation. The FALSE result is. The constraint is declared at the column level or the table level. It can be added, dropped, and validated with ALTER TABLE. It is the most flexible of the column-level constraints, and it is the tool that lets the database enforce the rules the application would otherwise have to enforce itself.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!