Keys That Keep Their Word

Primary and foreign keys recap, then the referential actions that decide a child's fate when its parent is deleted — CASCADE, SET NULL, RESTRICT, NO ACTION, and ON UPDATE — each triggered against a real parent/child pair. Plus composite keys, natural vs surrogate, and DEFERRABLE. Run against PostgreSQL 18.

Normalization split the bookshop into tables that point at each other, and the previous chapter closed on the obligation that split creates: the pointers have to stay honest. A foreign key is how the database keeps them honest. You met it in the fundamentals series as the constraint that refuses a dangling pointer. This chapter is about the part that’s easy to skip and expensive to get wrong: what the database does to the children when you delete their parent. That single choice, made once in the schema, is the difference between a clean delete and a wall of foreign-key errors. Or worse: a pile of orphaned rows nobody notices for a year.

Keys, recapped

A primary key uniquely identifies a row. It’s UNIQUE and NOT NULL together, and every table should have exactly one. A foreign key is a column (or set of columns) in a child table whose values must match a primary key in a parent table. order_items.order_id references orders.order_id; that reference is a promise the engine keeps, so a join from a line to its order can never come back empty by accident.

The promise runs in both directions. You can’t insert a child pointing at a parent that doesn’t exist. And you can’t delete a parent out from under its children without telling the database what should happen to them. That second direction is this chapter’s subject.

The default: refuse

Set up a parent and a child with a plain foreign key, no action specified:

CREATE TABLE scratch.ord (
    ord_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    label  text NOT NULL
);
CREATE TABLE scratch.line_na (
    line_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ord_id  integer NOT NULL REFERENCES scratch.ord(ord_id),  -- NO ACTION, the default
    item    text NOT NULL
);

Insert an order and a line, then try to delete the order:

DELETE FROM scratch.ord WHERE ord_id = 4;
ERROR:  update or delete on table "ord" violates foreign key constraint "line_na_ord_id_fkey" on table "line_na"
DETAIL:  Key (ord_id)=(4) is still referenced from table "line_na".

This is NO ACTION, the default when you say nothing. The delete is refused because a child still points at the row. It’s the safe choice, and it forces you to decide deliberately what else you might have wanted. There are three other answers, and each is a phrase you add to the foreign key.

ON DELETE CASCADE: take the children with it

ON DELETE CASCADE means: when the parent goes, the children go too. Declare it on the child’s foreign key:

CREATE TABLE scratch.line (
    line_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ord_id  integer NOT NULL REFERENCES scratch.ord(ord_id) ON DELETE CASCADE,
    item    text NOT NULL
);

With three lines across two orders, delete order 1:

DELETE FROM scratch.ord WHERE ord_id = 1;
SELECT * FROM scratch.line ORDER BY line_id;
 line_id | ord_id |  item
---------+--------+--------
       3 |      2 | book z

Both of order 1’s lines are gone, order 2’s line survives. This is exactly right for a true parent/child relationship where the child cannot exist without the parent. Order lines are meaningless without their order, so deleting the order should sweep them up. Use it where the child is part of the parent. Be very careful using it anywhere else: CASCADE deletes are silent and they chain, so a single DELETE can quietly remove rows three tables away.

ON DELETE SET NULL: cut the tie, keep the child

Sometimes the child should outlive the parent, just without the link. ON DELETE SET NULL blanks the foreign key instead of deleting the row. The column has to be nullable for this to work:

CREATE TABLE scratch.line_sn (
    line_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ord_id  integer REFERENCES scratch.ord(ord_id) ON DELETE SET NULL,  -- nullable
    item    text NOT NULL
);

Delete the parent, and the child stays with a null pointer:

DELETE FROM scratch.ord WHERE ord_id = 2;
SELECT * FROM scratch.line_sn ORDER BY line_id;
 line_id | ord_id |  item
---------+--------+--------
       1 |        | book z

The line is still there; ord_id is now null. This fits a soft relationship — a book whose author record is removed can keep existing as an orphan attributed to nobody, rather than vanishing. Postgres 15 added a companion, ON DELETE SET DEFAULT, which sets the column to its column default instead of null.

RESTRICT vs NO ACTION: two ways to refuse

ON DELETE RESTRICT also blocks the delete, and its error even names the setting:

DELETE FROM scratch.ord WHERE ord_id = 3;
ERROR:  update or delete on table "ord" violates RESTRICT setting of foreign key constraint "line_r_ord_id_fkey" on table "line_r"
DETAIL:  Key (ord_id)=(3) is referenced from table "line_r".

So what’s the difference from NO ACTION, which we saw refuse the same way? Timing. NO ACTION checks the constraint at the end of the statement (or transaction, if deferred), which leaves room for another action earlier in the same transaction to fix things up first. RESTRICT checks immediately and cannot be deferred. For most schemas they behave identically, and NO ACTION (the default) is the one you’ll write; reach for RESTRICT only when you specifically want the check to be un-deferrable.

ON UPDATE: when the parent’s key itself changes

Everything so far was about deleting a parent. ON UPDATE governs the rarer case where the parent’s key value changes. With a surrogate identity key you’ll almost never do this, but with a natural key it happens. Give shelves a text code and let books reference it with ON UPDATE CASCADE:

CREATE TABLE scratch.shelf (
    code text PRIMARY KEY,
    name text NOT NULL
);
CREATE TABLE scratch.shelf_book (
    book       text NOT NULL,
    shelf_code text NOT NULL REFERENCES scratch.shelf(code) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (book, shelf_code)
);

Rename the shelf code from SCI to SFF, and the children follow automatically:

UPDATE scratch.shelf SET code = 'SFF' WHERE code = 'SCI';
SELECT * FROM scratch.shelf_book ORDER BY book;
    book    | shelf_code
------------+------------
 Dune       | SFF
 Foundation | SFF

Both rows re-pointed with one update. That shelf_book table also shows a composite key: its primary key is the pair (book, shelf_code), because neither column is unique alone but the combination is. Composite keys are how you model a many-to-many link, and the bookshop’s order_items uses exactly one, keyed on (order_id, book_id).

Natural vs surrogate keys

The shelf example used a natural key — code, a value with meaning in the real world. Most of the bookshop uses surrogate keys — GENERATED ALWAYS AS IDENTITY integers with no meaning beyond identifying a row. The trade is straightforward. A surrogate key never changes, so ON UPDATE is moot and joins are cheap integer comparisons. The cost is an extra opaque column and a lookup to find the id you want. A natural key is human-readable and needs no lookup. The cost is that real-world identifiers change — an ISBN gets reassigned, an email is updated — and when they do you need ON UPDATE CASCADE and you pay for it across every child. The common default, and the bookshop’s, is a surrogate primary key everywhere, with a UNIQUE constraint on the natural identifier alongside it.

DEFERRABLE, in one breath

By default a foreign key is checked at the end of each statement. A DEFERRABLE INITIALLY DEFERRED key waits until COMMIT, which lets you insert a child before its parent inside one transaction — useful for loading circularly-referencing data:

BEGIN;
INSERT INTO scratch.child_d VALUES (1, 100);   -- parent 100 doesn't exist yet
INSERT INTO scratch.parent_d VALUES (100);      -- now it does
COMMIT;                                          -- check runs here, passes

That commits cleanly; the constraint is satisfied by the time the transaction ends, which is all a deferred check asks.

Final thoughts

A foreign key is a promise, and the referential action is the fine print on what happens when the promised-to row is deleted or its key changes. NO ACTION and RESTRICT refuse; CASCADE propagates; SET NULL (and SET DEFAULT) cuts the tie and keeps the child. The choice isn’t cosmetic — it decides whether deleting a customer wipes their orders, blocks you cold, or leaves a trail of orphans. Pick it deliberately per relationship, lean on CASCADE only where the child truly belongs to the parent, and prefer surrogate keys so ON UPDATE rarely comes up at all. With the tables normalized and the keys keeping their word, you have a schema worth building interfaces on top of. The next one is the simplest and most useful of those: a query you can give a name.

Next: A query with a name: views — CREATE VIEW as a stable interface, updatable views, and WITH CHECK OPTION.

Comments