UNIQUE and CHECK

A primary key makes sure that no two rows have the same ID. Often, another column must be unique too: two customers can't share an email, or the shop wouldn't know whose account it is. UNIQUE after the column's type says that. The shop's tournaments could keep their players like this:

CREATE TABLE players (
    id INTEGER PRIMARY KEY,
    nickname TEXT NOT NULL UNIQUE,
    age INTEGER NOT NULL CHECK (age >= 13)
) STRICT;

A second player with a nickname that's already taken is refused. Neither of the two rows is inserted: a statement that fails changes nothing.

INSERT INTO players (nickname, age)
VALUES ('NightOwl', 15),
       ('NightOwl', 22);

Error on line 1: UNIQUE constraint failed: players.nickname

UNIQUE allows NULL, even many times: NULL means no value, and one missing value isn't equal to another. When the column must always have a value, add NOT NULL, as above. The email of the shop's customers is TEXT NOT NULL UNIQUE too.

CHECK (followed by a condition in brackets) sets a rule that every row must meet. The condition is written as in WHERE, and the tournaments above are for players aged 13 and older:

INSERT INTO players (nickname, age)
VALUES ('TinyTim', 11);

Error on line 1: CHECK constraint failed: age >= 13

The shop's tables use CHECK too: the stars of a review must be BETWEEN 1 AND 5, the quantity > 0, and the status of an order IN ('paid', 'refunded'). A CHECK that compares two columns, or a UNIQUE for two columns together, is written after the last column, on a line of its own:

CREATE TABLE tournaments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    starts TEXT NOT NULL,
    ends TEXT NOT NULL,
    CHECK (ends >= starts),
    UNIQUE (name, starts)
) STRICT;

UNIQUE (name, starts) allows two tournaments with the same name, and two that start on the same day, but not two with the same name on the same day.

Typical mistake: forgetting that CHECK lets NULL through. With NULL in age, the condition age >= 13 is neither true nor false, and CHECK refuses a row only when its condition is false. That's why a column with a CHECK usually needs NOT NULL too.