Rules the database keeps

A primary key is a row's name

PRIMARY KEY means unique and never NULL: every row has one address, and no two rows share it. A natural key is a value the world already provides — an ISBN, a country code — while a surrogate key is a number the database makes up. Surrogate keys are usually the pragmatic choice, because real-world identifiers have a way of turning out to be neither unique nor stable.

The modern way to make one is id integer GENERATED ALWAYS AS IDENTITY, which assigns 1, 2, 3 as rows arrive and refuses to accept a hand-written value unless you insist. VARCHAR and bigint work here too; the point is that the database produces the value, so two clients inserting at the same moment cannot collide.

Rules beyond the key

UNIQUE on a column makes duplicate values an error — one active subscription per address, one username per account. CHECK states a condition every row must satisfy: price numeric(10, 2) CHECK (price >= 0) makes a negative price impossible, whatever code writes it. The database does not care whether the mistake came from a new feature, a manual fix or a script from three years ago.

A FOREIGN KEY ties a column to another table's key: customer_id integer REFERENCES customers(id) means an order can only name a customer who exists. Attempting to insert an order for customer 999 is an error, and so is deleting a customer who still has orders — unless the reference says ON DELETE CASCADE, which removes the orders along with the customer.

Why put rules here

Application code is one writer among many: a background job, an import script, an admin console opened once a month. A rule in the database holds for all of them, and it holds for the version of the code that has not been written yet. That is the trade: constraints are less convenient at the moment you add them and much less expensive than the data they prevent.

This chapter deliberately breaks things. Each failing statement prints its error in the report — that error is the feature working, and reading it is how you know which rule refused and why.

When a rule is checked

Most constraints are checked at the end of the statement that changes the row, before the statement is allowed to finish. A UNIQUE constraint is backed by an index, so the check is a lookup rather than a scan. A foreign key is enforced by a trigger on both sides: the child insert must find a parent, and the parent delete must not orphan a child.

Because the check is immediate, a failing INSERT stores nothing — but an identity sequence still advances. That is why generated ids have gaps after failed inserts, and why a gap is not evidence that a row was deleted. Sequences are counters, not a record of what exists.

What happens to the children

A foreign key has to say what a parent delete means. The default, NO ACTION, refuses the delete if children remain, and RESTRICT does the same a little earlier. CASCADE deletes the children too. SET NULL keeps them and clears the reference, which requires the column to allow NULL.

None of these choices is universally right. Cascade is convenient for rows that have no meaning without their parent, such as order lines. Restrict is safer for anything with its own history, such as invoices. The important part is that the schema says which one, rather than leaving it to the next person to guess.

Try these next

• Name a constraint explicitly and read the error it produces when it refuses a row.
• Add a composite UNIQUE (member_id, plan_id) and try to insert the same pair twice.
• Change a foreign key from CASCADE to RESTRICT and watch the parent delete fail.

A schema that refuses bad data

CREATE TABLE customers (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL,
    credit numeric(10, 2) NOT NULL DEFAULT 0 CHECK (credit >= 0)
);

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    total numeric(10, 2) NOT NULL CHECK (total > 0),
    placed_at timestamptz NOT NULL DEFAULT now()
);

-- The id is produced by the database, not by us.
INSERT INTO customers (email, name) VALUES ('[email protected]', 'Ada')
RETURNING id, name;

-- The same email again: refused by UNIQUE.
INSERT INTO customers (email, name) VALUES ('[email protected]', 'Ada Again');

-- Negative credit: refused by CHECK.
INSERT INTO customers (email, name, credit) VALUES ('[email protected]', 'Alan', -5);

-- An order for a customer who does not exist: refused by the FOREIGN KEY.
INSERT INTO orders (customer_id, total) VALUES (999, 10.00);

-- An order that follows every rule.
INSERT INTO orders (customer_id, total) VALUES (1, 42.50) RETURNING id, total;

-- Deleting the customer takes their orders with it.
DELETE FROM customers WHERE id = 1 RETURNING email;

SELECT count(*) AS orders_left FROM orders;