PostgreSQL ON DELETE CASCADE: Foreign Keys With Examples
Learn how postgresql on delete cascade works — auto-delete child rows, CASCADE vs RESTRICT vs SET NULL, and fixing the 23503 error, with runnable SQL.
Every database with more than one table eventually hits the same problem: you delete a row in a parent table, and rows in a child table still point at it. Understanding postgresql on delete cascade — and its siblings RESTRICT , SET NULL , and SET DEFAULT — is how you stop orphaned rows from piling up in your database. This guide walks you through foreign keys from scratch: what they guarantee, how each ON DELETE action behaves, and the fixes for the errors you'll actually see in the wild. What you'll learn What a foreign key guarantees (referential integrity) and how ON DELETE actions work How to add a foreign key to an existing table in Postgres with ALTER TABLE ON DELETE CASCADE vs RESTRICT vs SET NULL : when to use each How to fix "update or delete on table violates foreign key constraint" (error 23503) How to change an existing foreign key to ON DELETE CASCADE Why PostgreSQL does not auto-create an index for foreign keys (and when to add one) Step 1 — Create a parent and child table A foreign key says: "values in this column must exist in that other table's column." Let's build a classic example — authors and books — where every book must belong to a real author: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE books ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE ); The REFERENCES authors(id) clause is the foreign key. ON DELETE CASCADE is the action that fires when a parent row is deleted: delete the child rows too, automatically. Load some data so every example below is self-contained: INSERT INTO authors (name) VALUES ('Agatha Christie'), ('Isaac Asimov'); INSERT INTO books (title, author_id) VALUES ('Murder on the Orient…