What Do CHECK Constraints Enforce?

A CHECK constraint evaluates a boolean expression on each row as it is written, and rejects the row when the expression is definitively false. That makes it the tool for rules that are about a single row's own values: a price that must not be negative, a discount that must be between 0 and 100, a status that must be one of an allowed set.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  price_cents INTEGER NOT NULL,
  discount_pct INTEGER,
  CONSTRAINT chk_price CHECK (price_cents >= 0),
  CONSTRAINT chk_discount CHECK (discount_pct BETWEEN 0 AND 100)
);

INSERT INTO products (id, name, price_cents, discount_pct) VALUES
  (1, 'Mechanical Keyboard', 7950, 10),
  (2, 'USB-C Hub', 3200, NULL);

SELECT id, name, discount_pct FROM products ORDER BY id;

Two rows come back. The second product's discount is NULL and it was accepted — that is the behaviour to understand, and it is covered below.

CHECK can compare a column against another column in the same row, so it can express rules like 'the end date must be after the start date' and 'the discount must not exceed the price'. It can reference expressions and function calls. What it cannot do is reference another table — a CHECK cannot ask whether a customer exists, because that is a foreign key's job, and it cannot look at other rows, so it cannot express 'the total must not exceed the sum of its lines'. Anything involving another row needs a trigger, a foreign key, or an assertion in the application.

CHECK constraints are also not a substitute for the type system. They are evaluated by the engine on every write, which is a real cost on a hot table, so it is worth keeping the expressions simple and worth not stacking a dozen of them on the same column when one would do.

The constraint stops the bad row at write time, and the detection query finds the bad rows that are already in a table that never had the constraint.

CREATE TABLE legacy_bookings (
  id INTEGER PRIMARY KEY,
  room_id INTEGER NOT NULL,
  check_in TEXT NOT NULL,
  check_out TEXT NOT NULL
);

CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,
  room_id INTEGER NOT NULL,
  check_in TEXT NOT NULL,
  check_out TEXT NOT NULL,
  CONSTRAINT chk_dates_ordered CHECK (check_out > check_in)
);

INSERT INTO legacy_bookings (id, room_id, check_in, check_out) VALUES
  (1, 1, '2026-03-01', '2026-03-05'),
  (2, 2, '2026-03-05', '2026-03-01'),
  (3, 3, '2026-03-07', '2026-03-09');

INSERT INTO bookings (id, room_id, check_in, check_out) VALUES
  (1, 1, '2026-03-01', '2026-03-05');

-- Adding the constraint to legacy_bookings would fail on row 2 with
--   CHECK constraint failed: chk_dates_ordered
-- The detection query finds the offenders before you try:
SELECT id, check_in, check_out
FROM legacy_bookings
WHERE NOT (check_out > check_in);

One row comes back from the detection query: booking 2, where the check-out date is before the check-in date. Booking 1 and booking 3 are fine.

The pattern this illustrates is the one you need for any constraint you add to an existing table. Adding a CHECK constraint to a table that already has rows is an ALTER TABLE operation, and in most engines it validates the existing rows as part of the change — so it fails, the constraint is not added, and you are left with a failed migration rather than a broken schema. The fix is always the same: find the offending rows first, repair or delete them, and only then add the constraint.

The detection query has one subtlety. It uses NOT (check_out > check_in) rather than check_out <= check_in on purpose, because NOT expresses the negation of the constraint exactly. If any of the columns were NULL, the constraint would evaluate to UNKNOWN and the row would be allowed in — and NOT of UNKNOWN is still UNKNOWN, so the query would not report it. Writing the check as WHERE check_out IS NULL OR check_in IS NULL OR check_out <= check_in finds every row the constraint would let through that you probably do not want.