Rules in the table
Constraints are rules written into a table’s definition. The DBMS checks every insert, update and delete against them. A change that breaks any rule is refused whole, so bad data never gets in, whichever program sends it.
One row, five rules
A primary key names each row: its value must be unique and never empty. The desk sends Stardust with id 2, but Coraline already has id 2. PostgreSQL refuses: duplicate key.
Next try: id 4, but no title. NULL means “no value here”. The title column is NOT NULL, so the row is refused. Every book must have a title.
shelf is UNIQUE: no two books may share a shelf spot. This try puts Stardust on B1, which Coraline already holds, so it is refused. Unlike a primary key, a UNIQUE column may be left empty.
CHECK (year > 0) is a test every row must pass. A typo sends year 0. The test 0 > 0 is false, so the row is refused. A CHECK can test any condition on the row’s own columns.
Keys link tables
author_id REFERENCES authors (id): it must match an id in the authors table. A foreign key is that link. Author 9 does not exist, so the DBMS refuses. No book can point at nobody.
Last try: id 4, title Stardust, shelf B2, year 1999, author 2. Author 2 is Neil Gaiman, so every rule passes. The DBMS stores the row and books grows to 4 rows.
Now the desk tries to delete Neil Gaiman. Coraline and Stardust still point at author 2, so the foreign key refuses the delete too. Move or delete those books first.
What happens
;; step 1/8 · rules
the books table and its rules:
CREATE TABLE books (
id integer PRIMARY KEY,
title text NOT NULL,
shelf text UNIQUE,
year integer CHECK (year > 0),
author_id integer
REFERENCES authors (id)
);
PRIMARY KEY one unique, non-empty id per row
NOT NULL a value is required
UNIQUE no two rows share a value
CHECK a test every row must pass
REFERENCES must match a row in another table
Each try in the figure breaks exactly one rule. A row that breaks several gets one error: PostgreSQL stops at the first rule it finds broken. A CHECK passes when its test is true or NULL, so CHECK (year > 0) lets a missing year through. Add NOT NULL as well to require one.