Part 3 of 5 · 1 min read
CHECK: a custom business rule
A condition on a column's value, beyond simple uniqueness or NOT NULL.
Think of a form that rejects a submitted age if it is a negative number, enforced right at the counter, before the form is ever filed.
In plain words: CHECK declares a condition every row must satisfy. Any insert or update that would violate it is rejected. CHECK can reference more than one column of the same row, not just one.
A worked example. A book's price can never be negative, and its discount can never exceed 100 percent.
The key lines.
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
price NUMERIC(6,2) NOT NULL CHECK (price >= 0),
discount NUMERIC(4,1) NOT NULL DEFAULT 0 CHECK (discount BETWEEN 0 AND 100)
);
CHECK constraint
A CHECK constraint declares a condition every row must satisfy, rejecting any insert or update that would violate it.
Example: CHECK (price >= 0) rejects any attempt to insert or update a book with a negative price.