SQL CHECK constraints

CHECK constraints are a powerful way to control data stored in a SQL database. Combined with choosing the appropriate type for we can enforce that the data written adheres to our business rules.

Taking PostgreSQL as our SQL database of choice, the docs show some basic examples, i.e.

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

Here the schema imposes three different but related constraints, namely that the price and discounted price of a product must not be negative amounts if they exist and that the discounted price must be less than the normal price. The docs stop here but recently I found myself wanting to express something like: if the price is less than an amount n, there cannot be a discount price. Essentially I wanted to express a conditional constraint on a column that depends on the value of another one.

There’s no examples of CHECK constraints using conditional expressions and I wasn’t totally sure if the resulting expression would actually be a valid SQL expression; it turns out it does and with hindsight it makes total sense. Knowing that, we can express that business rule in a straightforward way:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price),
    CONSTRAINT products_bargain_no_discount
    CHECK (CASE WHEN price < 1 THEN discounted_price IS NULL END)
);

Now if we try to insert a product that with a bargain and a discounted price, the INSERT statement will be rejected:

postgres=# INSERT INTO products VALUES (1,'test',0.99,0.55);
ERROR:  new row for relation "products" violates check constraint "products_bargain_no_discount"
DETAIL:  Failing row contains (1, test, 0.99, 0.55).