postgres://internals

Schema Design & Constraints

// the problem

A database's real job isn't just to store data — it's to refuse bad data. Constraints turn "please don't insert garbage" (an app-level hope that any buggy service, migration, or console session can break) into a guarantee the database enforces on every write, forever. Get them right and whole classes of bug simply can't happen.

Foreign keys are the constraint people fear most, because deleting a parent row raises a question: what happens to the children? Pick an ON DELETE action and find out — this is exactly the rule Postgres applies.

foreign keys · ON DELETE

ON DELETE— delete the author's books too

authors (parent)

#1 Ada
#2 Linus
#3 Grace

books (child · FK author_id)

#10 Sketchesauthor_id=1 · Ada
#11 Analytical Notesauthor_id=1 · Ada
#12 Just for Funauthor_id=2 · Linus

pick an action, then delete an author — watch the foreign key enforce it.

Now the real thing. This builds two related tables with a full set of constraints — a primary key, a foreign key, a CHECK, a UNIQUE, and NOT NULLs:

sql · live postgresrun me first — a constrained schema

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

CREATE TABLE authors (
id   int  PRIMARY KEY,
name text NOT NULL
);

CREATE TABLE books (
id        int     PRIMARY KEY,
title     text    NOT NULL,
author_id int     REFERENCES authors(id) ON DELETE CASCADE,
price     numeric CHECK (price > 0),
isbn      text    UNIQUE
);

INSERT INTO authors VALUES (1,'Ada'), (2,'Linus'), (3,'Grace');
INSERT INTO books VALUES
(1,'Sketches',        1, 29, '978-1'),
(2,'Analytical Notes',1, 39, '978-2'),
(3,'Just for Fun',    2, 19, '978-3');

SELECT * FROM books ORDER BY id;

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

CREATE TABLE authors (
id   int  PRIMARY KEY,
name text NOT NULL
);

CREATE TABLE books (
id        int     PRIMARY KEY,
title     text    NOT NULL,
author_id int     REFERENCES authors(id) ON DELETE CASCADE,
price     numeric CHECK (price > 0),
isbn      text    UNIQUE
);

INSERT INTO authors VALUES (1,'Ada'), (2,'Linus'), (3,'Grace');
INSERT INTO books VALUES
(1,'Sketches',        1, 29, '978-1'),
(2,'Analytical Notes',1, 39, '978-2'),
(3,'Just for Fun',    2, 19, '978-3');

SELECT * FROM books ORDER BY id;

⌘/ctrl + enter

Every constraint refuses bad data

Each of these writes is rejected by the database — the error tells you exactly which rule you broke. Run it, then uncomment the others one at a time:

sql · live postgrestry to break the rules

-- FK: there is no author 99
INSERT INTO books VALUES (9, 'Ghost', 99, 10, '978-9');

-- CHECK: price must be > 0
-- INSERT INTO books VALUES (9, 'Freebie', 1, -5, '978-9');

-- UNIQUE: that isbn is already taken
-- INSERT INTO books VALUES (9, 'Dup', 1, 10, '978-1');

-- NOT NULL: a book must have a title
-- INSERT INTO books (id, author_id, price, isbn) VALUES (9, 1, 10, '978-9');

-- FK: there is no author 99
INSERT INTO books VALUES (9, 'Ghost', 99, 10, '978-9');

-- CHECK: price must be > 0
-- INSERT INTO books VALUES (9, 'Freebie', 1, -5, '978-9');

-- UNIQUE: that isbn is already taken
-- INSERT INTO books VALUES (9, 'Dup', 1, 10, '978-1');

-- NOT NULL: a book must have a title
-- INSERT INTO books (id, author_id, price, isbn) VALUES (9, 1, 10, '978-9');

⌘/ctrl + enter

// why it matters · the database is the last line of defense

App-side validation is good UX, but it's not a guarantee — another service, a data migration, a psql session, or a bug can all write straight to the table. A constraint is checked on every write no matter where it comes from. Like row-level security, the rule that matters is the one the database enforces.

Foreign keys and referential actions

A foreign key says "this column must point at a real row over there." The ON DELETE action decides what happens to the children when the parent goes. Author 1 (Ada) wrote two books, and the key is ON DELETE CASCADE:

sql · live postgrescascade the delete

DELETE FROM authors WHERE id = 1;      -- Ada's two books go with her
SELECT id, title, author_id FROM books ORDER BY id;

DELETE FROM authors WHERE id = 1;      -- Ada's two books go with her
SELECT id, title, author_id FROM books ORDER BY id;

⌘/ctrl + enter

Only Just for Fun survives. The other two were cascade-deleted.

// note · choosing an ON DELETE action

CASCADE — delete the children too. Right for true ownership (order → order-items).
RESTRICT / NO ACTION — refuse the delete while children exist (the default). Safest: it forces you to deal with the children first.
SET NULL — keep the children but orphan them (the column becomes NULL). For updates there's a matching ON UPDATE. Pick per relationship, not per habit.

UNIQUE is an index (and PRIMARY KEY is both)

A UNIQUE constraint is enforced by a unique B-tree index — the same structure from the indexing lesson — so it doubles as a fast lookup path. A PRIMARY KEY is just UNIQUE + NOT NULL. Both created indexes automatically:

sql · live postgreseditable — run it

SELECT indexname FROM pg_indexes WHERE tablename = 'books' ORDER BY indexname;

SELECT indexname FROM pg_indexes WHERE tablename = 'books' ORDER BY indexname;

⌘/ctrl + enter

Generated columns

Some columns are derived from others. Rather than compute a total in the app (and risk it drifting), let the database keep it correct — a GENERATED ALWAYS AS (...) STORED column is recomputed on every write and can't be set by hand:

sql · live postgreseditable — run it

DROP TABLE IF EXISTS invoice;
CREATE TABLE invoice (
qty   int,
price numeric,
total numeric GENERATED ALWAYS AS (qty * price) STORED
);
INSERT INTO invoice (qty, price) VALUES (3, 9.99);
SELECT * FROM invoice;   -- total is 29.97, computed for you

DROP TABLE IF EXISTS invoice;
CREATE TABLE invoice (
qty   int,
price numeric,
total numeric GENERATED ALWAYS AS (qty * price) STORED
);
INSERT INTO invoice (qty, price) VALUES (3, 9.99);
SELECT * FROM invoice;   -- total is 29.97, computed for you

⌘/ctrl + enter

Normalize: store each fact once

The reason books references authors by id — instead of copying the author's name into every book — is normalization: each fact lives in exactly one place. Change an author's name once and every book reflects it; there's no way for two rows to disagree. Foreign keys are what make that reference trustworthy.

// note · EXCLUDE — the constraint you haven't met

Beyond UNIQUE there's EXCLUDE, which forbids rows that conflict by any operator, not just equality. The classic use is "no two bookings for the same room may overlap" — EXCLUDE USING gist (room WITH =, during WITH &&). It needs a GiST index (and the btree_gist extension), so it isn't runnable here, but it's the tool when "no overlaps" is the rule.

Inspect the constraints

Every constraint is a row in pg_constraint. The contype tells you the kind — primary, foreign, check, unique, not-null:

sql · live postgreseditable — run it

SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'books'::regclass
ORDER BY contype;

SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'books'::regclass
ORDER BY contype;

⌘/ctrl + enter

Your turn

// challengewrite it yourself

This DELETE fails — the foreign key refuses to orphan the child rows. Change the foreign key so deleting a parent removes its children automatically, then report how many child rows remain.

DROP TABLE IF EXISTS child9;
DROP TABLE IF EXISTS parent9;
CREATE TABLE parent9 (id int PRIMARY KEY);
-- make this key cascade deletes down to child9:
CREATE TABLE child9 (id int PRIMARY KEY, pid int REFERENCES parent9(id));
INSERT INTO parent9 VALUES (1), (2);
INSERT INTO child9 VALUES (10,1), (11,1), (20,2);
DELETE FROM parent9 WHERE id = 1;
SELECT count(*) AS remaining FROM child9;

DROP TABLE IF EXISTS child9;
DROP TABLE IF EXISTS parent9;
CREATE TABLE parent9 (id int PRIMARY KEY);
-- make this key cascade deletes down to child9:
CREATE TABLE child9 (id int PRIMARY KEY, pid int REFERENCES parent9(id));
INSERT INTO parent9 VALUES (1), (2);
INSERT INTO child9 VALUES (10,1), (11,1), (20,2);
DELETE FROM parent9 WHERE id = 1;
SELECT count(*) AS remaining FROM child9;

⌘/ctrl + enter

// challengewrite it yourself

Add a constraint so a product's price can never be negative. The query counts CHECK constraints on the table — make it report 1.

DROP TABLE IF EXISTS product9;
CREATE TABLE product9 (
id    int PRIMARY KEY,
price numeric        -- forbid negative prices here
);
SELECT count(*) AS checks
FROM pg_constraint
WHERE conrelid = 'product9'::regclass AND contype = 'c';

DROP TABLE IF EXISTS product9;
CREATE TABLE product9 (
id    int PRIMARY KEY,
price numeric        -- forbid negative prices here
);
SELECT count(*) AS checks
FROM pg_constraint
WHERE conrelid = 'product9'::regclass AND contype = 'c';

⌘/ctrl + enter

// what you now understand

01Constraints make bad data impossible at the database level — enforced on every write regardless of which client or service does it.
02PRIMARY KEY = UNIQUE + NOT NULL; a UNIQUE constraint is enforced by (and doubles as) a unique B-tree index.
03A foreign key requires the referenced row to exist; ON DELETE CASCADE / RESTRICT / SET NULL decide what happens to children when the parent is deleted.
04CHECK enforces per-row (or cross-column) rules; NOT NULL forbids missing values.
05Generated columns (GENERATED ALWAYS AS ... STORED) keep derived values correct automatically and can't be set by hand.
06Normalization stores each fact once and references it with foreign keys, so rows can never disagree.
07EXCLUDE constraints forbid conflicting (not just equal) rows — e.g. non-overlapping bookings — via a GiST index.
08pg_constraint lists every constraint; contype is p/f/c/u/n for primary/foreign/check/unique/not-null.

// self-test

You have orders and order_items, where each item belongs to one order. A user deletes an order. What ON DELETE action on order_items.order_id usually fits?

// self-test

Why enforce 'price must be positive' with a CHECK constraint instead of validating it in your API?

// go deeper

Constraints (official docs) — CHECK, NOT NULL, UNIQUE, primary and foreign keys, EXCLUDE
Foreign Keys — referential integrity and ON DELETE / ON UPDATE actions
Generated Columns — STORED generated columns
pg_constraint — the catalog behind every constraint

nextJSON & JSONB