CHECK
A bug in the checkout code sends quantity = -3 to the database, and the shop's stock count goes up when a customer "buys" something. The code will be fixed, but the next bug is already waiting. A CHECK constraint writes the business rule into the table itself, where every writer has to obey it.
The rule
CHECK states a rule that no row may break.
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
discount DECIMAL(10,2) DEFAULT 0,
CONSTRAINT discount_below_price CHECK (discount <= price)
);A CHECK holds a condition, just like a WHERE clause. On every INSERT and UPDATE, the database evaluates it for the new row; if the condition is false, the change is rejected.
- A column CHECK sits next to one column:
CHECK (price >= 0). - A table CHECK can compare several columns of the same row:
CHECK (discount <= price). Giving it a name withCONSTRAINT …makes error messages readable and lets you drop it later.
Common uses in the shop:
quantity INTEGER NOT NULL CHECK (quantity > 0)
status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'shipped', 'cancelled'))
rating INTEGER CHECK (rating BETWEEN 1 AND 5)The one surprise is NULL. A CHECK rejects a row only when the condition is false. If rating is NULL, rating BETWEEN 1 AND 5 is unknown, not false, so the row is accepted. When a value is required as well as valid, add NOT NULL.
Two practical limits: a CHECK sees only the row being written — it cannot look at other rows or other tables (that is a foreign key's job). And MySQL versions before 8.0.16 parsed CHECK but silently ignored it, so on legacy systems confirm it is enforced.
The shortcut
Take the business rule in plain words and rewrite it as a WHERE condition that every good row would pass. That condition is your CHECK.
Business rule
"You cannot order zero or fewer items."
Drills
Answer fast — trust the rule.
quantity INTEGER CHECK (quantity > 0). What happens on an insert with quantity 0?
rating INTEGER CHECK (rating BETWEEN 1 AND 5) with no NOT NULL. Is a row with rating NULL accepted?
Which CHECK limits status to the three allowed values?
Can CHECK (customer_id IN (SELECT customer_id FROM customers)) enforce that an order's customer exists?
Remember
- CHECK states a rule that no row may break.
- Rewrite the business rule as a WHERE condition good rows pass.
- NULL slips past CHECK (unknown is not false) — pair it with NOT NULL when the value is required.