SQL / DEFINING TABLES AND CONSTRAINTS
CHECK constraints and the rules they enforce
Write CHECK constraints that reject bad rows at write time, predict how NULL slips through them, and add one to a table that already holds data.
What you will learn
- Write named column-level and table-level CHECKs that judge one row at a time
- Predict whether a CHECK accepts a row, including when the tested column is NULL
- Add a CHECK to a populated table and find the rows that will block it first
- Recognize rules CHECK cannot express, which need UNIQUE, a key, or a trigger
Understanding CHECK constraints and the rules they enforce
A CHECK constraint is a boolean expression the engine evaluates against a single row every time that row is written. Put it after a column when it mentions only that column, and after the last column when it compares two columns of the same row; the placement does not change when it runs, only how the rule reads. Prefix every one with CONSTRAINT and a name, because the name is what shows up in the error message, and a generated name like room_booking_check2 tells the person reading the log nothing. A failed CHECK aborts the whole statement, so a five-row INSERT that trips on the fifth row stores none of the five.
The acceptance rule is looser than "the expression is true": a row is rejected only when the expression evaluates to false. Any NULL operand makes a comparison unknown, and unknown is not false, so CHECK (weight_kg > 0) cheerfully stores a row with no weight at all. That is why a range rule and NOT NULL usually travel together, or you fold the test in yourself: CHECK (weight_kg IS NOT NULL AND weight_kg > 0) evaluates to false, not unknown, when the value is missing.
A CHECK can only see the row in front of it: no subqueries, no other rows, no other tables, and nothing that shifts on its own such as the clock. The reason is that the engine evaluates the expression only when that row is written and once more when the constraint is first added; it will not revisit row 4000 because row 4001 arrived or because midnight passed. Rules that span rows belong to UNIQUE, foreign keys, or triggers. CHECK is for facts a row can be judged on alone: a fee is not negative, a status is one of five strings, a date range does not run backwards.
CREATE TABLE room_booking (
id integer PRIMARY KEY,
guests integer NOT NULL CONSTRAINT guests_at_least_one CHECK (guests >= 1),
nightly_fee numeric(6,2) NOT NULL CONSTRAINT fee_not_negative CHECK (nightly_fee >= 0),
checkin date NOT NULL,
checkout date NOT NULL,
CONSTRAINT stay_ends_after_it_starts CHECK (checkout > checkin)
);
INSERT INTO room_booking VALUES (1, 2, 89.50, '2026-04-01', '2026-04-04');
INSERT INTO room_booking VALUES (2, 2, 89.50, '2026-04-04', '2026-04-01');
SELECT * FROM room_booking;A CHECK is a per-row boolean test, and it rejects the row only when the expression comes out false, so unknown results from NULL are let through.
Worked examples
NULL walks past a CHECK
Shows that a missing value makes the expression unknown, which is not a violation.
CREATE TABLE shipment (
id integer PRIMARY KEY,
weight_kg numeric(6,2) CHECK (weight_kg > 0)
);
INSERT INTO shipment VALUES (1, 2.50);
INSERT INTO shipment VALUES (2, NULL);
INSERT INTO shipment VALUES (3, 0);
SELECT count(*) FROM shipment;Example explained
Line 1Row 2 is stored because NULL > 0 is unknown, and only a false result rejects a row.
Line 2Row 3 is rejected because 0 > 0 is false; the failing row is shown as 0.00 after coercion to numeric(6,2).
Line 3The constraint had no name, so the engine invented shipment_weight_kg_check from the table and column.
Line 4count(*) is 2: the rejected insert left nothing behind, but the weightless row is now permanent data.
Adding a CHECK to a table that already has data
Shows that ALTER TABLE validates existing rows and refuses the constraint if any row fails.
CREATE TABLE invoice (
id integer PRIMARY KEY,
total numeric(8,2) NOT NULL
);
INSERT INTO invoice VALUES (1, 120.00), (2, -30.00);
SELECT id FROM invoice WHERE NOT (total >= 0);
ALTER TABLE invoice ADD CONSTRAINT total_not_negative CHECK (total >= 0);Example explained
Line 1WHERE NOT (total >= 0) finds exactly the rows the constraint would reject, because a NULL there would also be dropped by WHERE.
Line 2ADD CONSTRAINT scans the whole table before accepting the rule, so one legacy row of -30.00 stops the migration.
Line 3The error names the constraint but not the row, which is why you run the SELECT first.
Line 4ADD CONSTRAINT ... NOT VALID would guard future writes only, leaving old rows unchecked until VALIDATE CONSTRAINT.
A CHECK cannot ask about other rows
Shows the engine refusing a constraint whose expression looks beyond the row being written.
CREATE TABLE seat (
id integer PRIMARY KEY,
label text NOT NULL CHECK (label ~ '^[A-F][0-9]{1,2}$'),
CONSTRAINT under_capacity CHECK ((SELECT count(*) FROM seat) <= 300)
);Example explained
Line 1The label rule is accepted in principle: a pattern test reads only the row being inserted.
Line 2The capacity rule is rejected at CREATE TABLE time, before any data exists.
Line 3It has to be: existing rows are never re-tested, so a later DELETE or a concurrent INSERT would leave the answer quietly wrong.
Line 4A whole-table limit needs a trigger or a counter column on a parent row instead.
Important notes
MySQL before 8.0.16 parsed CHECK clauses and ignored them, so on an old server the rule is a comment; prove enforcement with a deliberately bad insert.
PostgreSQL will let you write CURRENT_DATE inside a CHECK and SQLite refuses non-deterministic functions outright, but either way the test runs only at write time, so a later dump and reload can fail on rows that were valid the day they arrived.
Common mistakes
Treating CHECK (discount_pct BETWEEN 0 AND 100) as a requirement to supply a discount: rows with NULL are accepted, and price * (1 - discount_pct / 100) then returns NULL for exactly those rows.
Leaving the constraint unnamed on a table with four CHECKs, so the production log says violates check constraint "orders_check2" and nobody can tell which rule the client broke.
Assuming a new CHECK applies only to future writes: ALTER TABLE ADD CONSTRAINT verifies every existing row and aborts the deploy on the first offender unless you add it NOT VALID.
Try it yourself
Change, predict, then run
Create product(id integer PRIMARY KEY, price numeric(8,2) NOT NULL, sale_price numeric(8,2)) with named CHECKs for price > 0 and sale_price < price, then insert three rows: a valid one, one whose sale_price exceeds price, and one with sale_price NULL. Predict which two survive before you run it, then explain the third.
Open the SQL workspaceCheck your understanding
A table is created with qty integer CHECK (qty > 0), and nothing else constrains qty. Which insert does the engine reject?
- An insert with qty = 1
- An insert with qty = NULL
- An insert that leaves qty out of the column list
- An insert with qty = 0
Show answer
0 > 0 evaluates to false, and false is the only result that rejects a row. NULL is the tempting answer, but NULL > 0 is unknown rather than false, so that row is stored; the insert that omits qty behaves the same way, since the column then holds NULL.