A CHECK constraint is a rule about the value in a column. The value has to satisfy an expression, or the row is refused. The NOT NULL says a value is there, the UNIQUE says nobody else has it, and the CHECK says the value is one of the ones that makes sense. It is the cheapest way to keep a range, a format or a relationship inside the database instead of inside a validation routine.
The previous lesson in this module covered "What Do NOT NULL, UNIQUE and DEFAULT Enforce?", and the next lesson in this module is about "How Do Auto-Increment and Sequences Generate IDs?".
How do the CHECK constraints catch the bad data early?
Start with a table of rated policies. The business gives you two rules. A premium cannot be negative, and a policy can only be active, lapsed or cancelled. Both rules look at a single column, so both belong in the column definition.
CREATE TABLE rated_policies (
policy_id INTEGER PRIMARY KEY,
policy_number TEXT NOT NULL UNIQUE,
premium REAL NOT NULL CHECK (premium > 0),
status TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'lapsed', 'cancelled'))
);A valid row goes in without any complaint.
INSERT INTO rated_policies (policy_id, policy_number, premium) VALUES (1, 'RP-001', 250.00);
SELECT policy_id, policy_number, premium, status FROM rated_policies;policy_id policy_number premium status
--------- ------------- ------- ------
1 RP-001 250.0 activeThe premium was positive, so the first check passed. The status was left out, so the default supplied the 'active' and the second check accepted it.
Now try a negative premium.
INSERT INTO rated_policies (policy_id, policy_number, premium) VALUES (2, 'RP-002', -5.00);CHECK constraint failed: premium > 0The engine names the expression it evaluated, and that is exactly the information a developer needs.
A status outside the allowed set is caught in the same way.
INSERT INTO rated_policies (policy_id, policy_number, premium, status) VALUES (3, 'RP-003', 100.00, 'paused');CHECK constraint failed: status IN ('active', 'lapsed', 'cancelled')The 'paused' is a perfectly reasonable word, but it is not one of the three states the insurer recognises, and the database refuses to invent a fourth.
A status column left as free text would have collected the 'Paused', the 'paused', the 'PAUSED' and the 'On hold' within a single season.
Can a CHECK compare two columns?
It can, and this is where the CHECK earns its keep.
A policy cannot end before it starts.
CREATE TABLE dated_policies (
policy_id INTEGER PRIMARY KEY,
start_date TEXT NOT NULL,
end_date TEXT NOT NULL CHECK (end_date > start_date)
);INSERT INTO dated_policies (policy_id, start_date, end_date) VALUES (1, '2026-01-01', '2026-12-31');
SELECT policy_id, start_date, end_date FROM dated_policies;policy_id start_date end_date
--------- ---------- ----------
1 2026-01-01 2026-12-31INSERT INTO dated_policies (policy_id, start_date, end_date) VALUES (2, '2026-01-01', '2025-12-31');CHECK constraint failed: end_date > start_dateThe text comparison works because the dates are stored in ISO format, so the alphabetical order of the text is also the order in time. Module 03 relied on the same property.
A column-to-column check is the only place where you can express a rule that spans two fields, without pushing the rule into a trigger or into the application code.
What happens when a check evaluates to a NULL?
Here is the subtlety that catches people out.
A CHECK constraint fails only when its expression evaluates to FALSE. If the expression evaluates to NULL, because one of the columns holds a NULL, the constraint passes.
The rule treats a missing value as "unknown" rather than as "bad". That is the usual three-valued logic of SQL, applied to constraints.
CREATE TABLE nullable_checks (id INTEGER PRIMARY KEY, amount INTEGER CHECK (amount > 0));
INSERT INTO nullable_checks (id, amount) VALUES (1, NULL);
SELECT id, amount FROM nullable_checks;id amount
-- ------
1 NULLThe row went in with the NULL amount.
The check asked "is the amount greater than zero", the answer was unknown, and unknown is not the same as false.
The practical result is that the CHECK does not replace the NOT NULL. To guarantee that a premium is present and positive you need both constraints, which is exactly why the rated_policies table declares its columns NOT NULL as well as CHECKed.
What can a CHECK not do?
Three limits are worth knowing before you reach for one.
A CHECK cannot contain a subquery. MySQL, PostgreSQL and SQL Server all reject it, and SQLite behaves differently again. So "this premium must be less than the holder's other policies" is not a CHECK. It is a trigger.
For the same reason, a CHECK cannot look at another table.
A CHECK is also evaluated row by row, so it cannot enforce a rule about a total across many rows. You use a trigger for that as well.
Triggers are the general answer when a rule really does need to look elsewhere, and every major engine supports them. They are harder to reason about than a constraint, and they fire in situations a developer has to remember. They fire on every insert, on every update, and on the delete of a row in the parent table.
The usual advice is to prefer a constraint, and to reach for a trigger only when nothing else will do.
Quick reference
- The
CHECKevaluates an expression for every row and refuses the write when the expression is false. ANULLresult passes. - You use it for ranges, for allowed value lists and for column-to-column comparisons.
- It fires on the
INSERTand on theUPDATE, so a bad value never exists even briefly. - A
CHECKcannot contain a subquery and cannot look at another table. You use a trigger for those rules. - You prefer a constraint over application validation, and over a trigger where a constraint will do.
Do the checks run on the updates too?
They do, and that is what makes a check a constraint rather than a validation.
Try setting a good premium to a negative one on a row that already exists.
UPDATE rated_policies SET premium = -1 WHERE policy_id = 1;CHECK constraint failed: premium > 0The change is refused, so the bad value never existed even for a moment.
A validation routine in the application code only protects the code path that calls it. A constraint protects the data.
Check drill: write the CREATE TABLE for a table of claims. It should refuse a claim amount that is not positive, allow only the statuses open, pending, settled and rejected, and refuse a claim that is dated before the start of the policy it belongs to. Then say which of those three rules a CHECK can enforce, and which one needs something else.