CSE Roadmap

Core tier · about 3 hours · 5 parts

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.