A CHECK constraint is a rule about the value of a column: it must satisfy an expression, or the row is refused. NOT NULL says a value is there; UNIQUE says nobody else has it; CHECK says it is one of the values that makes sense. It is the cheapest way to keep a range, a format or a relationship inside the database rather than in a validation routine.
How do CHECK constraints catch bad data early?
Consider a table of rated policies. Two rules from the business: a premium cannot be negative, and a policy can only be active, lapsed or cancelled. Both are single-column rules and 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 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 active
The premium was positive, so the first check passed; the status was omitted, so the default supplied 'active', and the second check accepted it. Now a negative premium:
INSERT INTO rated_policies (policy_id, policy_number, premium) VALUES (2, 'RP-002', -5.00);
CHECK constraint failed: premium > 0
The engine names the expression it evaluated, which is exactly the information a developer needs. And a status outside the allowed set is caught 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')
'paused' is a perfectly reasonable word, but it is not one of the three states the insurer recognises, and the database now refuses to invent a fourth. A status column left as free text would have accumulated 'Paused', 'paused', 'PAUSED' and 'On hold' within a season.
Can a CHECK compare two columns?
Yes, and this is where 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-31
INSERT INTO dated_policies (policy_id, start_date, end_date) VALUES (2, '2026-01-01', '2025-12-31');
CHECK constraint failed: end_date > start_date
Text comparison works because the dates are stored in ISO format, so lexicographic order is chronological order — the same property module 03 relied on. A column-to-column check is the only place you can express a rule that spans two fields without moving the rule into a trigger or into application code.
What happens when a check evaluates to 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 its columns is NULL — the constraint passes. The rule exists so that a missing value is treated as "unknown" rather than "bad", which is the standard SQL three-valued logic 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 NULL
The row went in, with a NULL amount. The check asked "is the amount greater than zero", the answer was unknown, and unknown is not false. The practical consequence is that CHECK does not replace NOT NULL: to guarantee that a premium is present and positive, you need both constraints, which is exactly why the rated_policies table declares the 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's behaviour differs. So "this premium must be less than the holder's other policies" is not a CHECK; it is a trigger. A CHECK cannot look at another table, for the same reason. And a CHECK is evaluated row by row, so it cannot enforce a rule about the total of many rows — use a trigger for that too.
Triggers are the general answer when a rule genuinely needs to look elsewhere, and every major engine supports them, but they are harder to reason about than a constraint and they fire in situations a developer must remember: on every insert, on every update, and on the delete of a row in the parent table. The default advice is to prefer a constraint and reach for a trigger only when there is no other option.
Quick reference
- CHECK evaluates an expression per row and refuses the write if it is false or NULL.
- Use it for ranges, allowed value lists and column-to-column comparisons.
- It fires on INSERT and on UPDATE, so bad values never exist even briefly.
- A CHECK cannot contain a subquery or look at another table — use a trigger for those rules.
- Prefer a constraint over application validation, and over a trigger where it will do.
Do checks run on updates too?
They do, and that is what makes a check a constraint rather than a validation. Try to set a good premium to a negative one on the row that already exists:
UPDATE rated_policies SET premium = -1 WHERE policy_id = 1;
CHECK constraint failed: premium > 0
Refused, so the bad value never existed for even a moment. A validation routine in application code only protects the code path that calls it; a constraint protects the data.
Check drill: write the CREATE TABLE for a claims table that refuses a non-positive claim amount, allows only the statuses open, pending, settled and rejected, and refuses a claim 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.