What do CHECK constraints enforce, and what can they not do?
A check constraint is a rule about one row, written as a condition that has to be true.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
price_cents INTEGER NOT NULL,
quantity INTEGER NOT NULL,
CHECK (price_cents >= 0),
CHECK (quantity >= 0)
);
So what does it enforce? That the price cannot be negative, and that the quantity cannot be negative. The database checks the rule every time a row is written or changed, and it refuses the row if the rule is broken.
And what can it not do? Four things.
It cannot look at another table. A check that says the order belongs to a real customer is not a check constraint, it is a foreign key.
It cannot look at other rows of the same table. A rule that says the end date is after the start date is fine, because both are in the same row. A rule that says the quantity of this product is more than the quantity of every other product is not a check constraint, because it needs another row.
It cannot use a subquery at all. So the rules that need one have to live in your program.
And there is one thing about missing values that catches people out. If the value that the condition uses is missing, then the condition works out to unknown, and an unknown result passes the check. So the column that the check reads has to be not null itself.
The next question in this topic shows the check catching bad data and finding the bad rows in old data.