CHECK constraints in SQL

A constraint is a rule the database will not let your code forget.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    subtotal REAL NOT NULL CONSTRAINT positive_subtotal CHECK (subtotal >= 0),
    discount REAL NOT NULL DEFAULT 0,
    status TEXT NOT NULL CHECK (status IN ('draft', 'sent', 'paid')),
    CONSTRAINT discount_within_subtotal CHECK (discount <= subtotal)
);

How it works

1. A named constraint puts its own name in the error message.
2. Column checks see one column; table checks see the whole row.
3. The check runs on every write, so it can never drift out of date.

Keywords and builtins used here

• CHECK
• CONSTRAINT
• CREATE
• DEFAULT
• IN
• INTEGER
• KEY
• NOT
• NULL
• PRIMARY
• REAL
• TABLE
• TEXT

The run, in numbers

Lines

7

Characters to type

290

Tokens

58

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 41 seconds.

Type this snippet

Step 1 of 5 in Constraints, step 1 of 16 in Schema & constraints.

Next →