Menu

PostgreSQL Foreign Keys and ON DELETE CASCADE

A foreign key makes a column only accept values that exist in another table. Covers REFERENCES, ON DELETE CASCADE, SET NULL and RESTRICT, adding a key to an existing table, and indexing it.

This page includes runnable editors. Edit, run, and see output instantly.

A foreign key ties a column to the primary key, or a unique column, of another table. PostgreSQL rejects any value that has no matching row there. Write it as column type REFERENCES other_table (column). Add ON DELETE CASCADE or ON DELETE SET NULL to say what happens to child rows when the parent is deleted.

The referenced column must have a PRIMARY KEY or UNIQUE constraint. With REFERENCES customers and no column, PostgreSQL uses the primary key of customers.

What a violation looks like

An order for a customer that does not exist is refused:

The error names the constraint, orders_customer_id_fkey, which is the name PostgreSQL gives an unnamed foreign key. Deleting a customer who still has orders fails the same way, unless the key says otherwise.

ON DELETE options

OptionWhen the parent row is deleted
NO ACTION (default)The delete fails while child rows exist
RESTRICTSame, but the check cannot be deferred to the end of the transaction
CASCADEThe child rows are deleted too
SET NULLThe child's foreign key column becomes NULL
SET DEFAULTThe child's column gets its default value

ON UPDATE takes the same options. It applies when the parent's key value changes, which is rare with generated ids.

CASCADE fits rows that make no sense without their parent, such as an order's line items:

Only chair is left. Cascades follow further keys, so one delete can remove rows in several tables.

SET NULL keeps the child row and detaches it:

Ada stays, with team_id set to NULL. Since PostgreSQL 15, ON DELETE SET NULL (column) can clear only some columns of a multi-column key.

Add a foreign key to an existing table

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

This checks every existing row and fails if any has no parent. On a large table, add the key with NOT VALID, which checks new rows only. Then run ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; to check the old rows without blocking writes. To change the ON DELETE action of an existing key, drop the constraint and add it again in one ALTER TABLE.

Index the foreign key column

PostgreSQL indexes the referenced side, not the referencing column. Without an index on orders.customer_id, every delete from customers scans all of orders to look for children, and so does fetching one customer's orders:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

To list the foreign keys of a table, query pg_constraint:

Joining across these keys is covered on the joins page, and the constraints chapter of the manual has the full rules, including deferrable keys.

Frequently Asked Questions

Does PostgreSQL create an index for a foreign key?

Not on the referencing column. The referenced column already has one, because it must be a primary key or unique. Create an index on the foreign key column yourself, since joins and deletes from the parent table search it.

How do I drop a foreign key in PostgreSQL?

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Unnamed foreign keys are called table_column_fkey; \d orders in psql shows the exact name.

Can a foreign key column be NULL in PostgreSQL?

Yes. A NULL in the foreign key column is not checked, so the row needs no parent. Add NOT NULL when every row must have one.

Can a foreign key reference the same table?

Yes. A column like manager_id int REFERENCES employees (id) points at another row of the same table. That is how trees such as org charts and comment threads are stored.

Coddy programming languages illustration

Learn to code with Coddy

Get started