Foreign key
What is a foreign key in PostgreSQL?
A foreign key requires values in one table to match a row in another, so you cannot point at a row that does not exist. ON DELETE decides what happens when that row goes away.
A foreign key constraint specifies that the values in a column (or a group of columns) must match the values in some row of another table. It keeps related tables consistent: an insert or update that points at a missing row is rejected.
The referenced columns must be a primary key or have a unique constraint. When a referenced row is removed, the ON DELETE action decides what happens: NO ACTION (the default) and RESTRICT reject the delete, CASCADE also removes the referencing rows, and SET NULL or SET DEFAULT change them. PostgreSQL does not index the referencing columns automatically, and adding an index there is often worthwhile.
Example
The example links invoices to customers with ON DELETE CASCADE. Removing the customer also removes its invoice, so the final count is 0.
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE invoices (
id int PRIMARY KEY,
customer_id int NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
amount numeric NOT NULL
);
INSERT INTO customers VALUES (1, 'Acme');
INSERT INTO invoices VALUES (10, 1, 99.50);
DELETE FROM customers WHERE id = 1;
SELECT count(*) FROM invoices;Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).