Foreign keys and what happens on delete

References in both directions

You have seen a foreign key refuse an order for a customer who does not exist. It also guards the other direction: you cannot delete a customer who still has orders.

DELETE FROM customers WHERE id = 1;

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

Asha has three orders, so deleting her would leave three orders pointing at nobody. Elena has none, so deleting her works:

DELETE FROM customers WHERE id = 5 RETURNING name;   -- Elena Petrova, DELETE 1

Choosing what a delete does

When you declare a foreign key, you choose what happens to the referencing rows when the referenced row is deleted:

ON DELETE	What happens to the orders when a customer is deleted	Use when
NO ACTION (default)	The delete fails while orders still exist	The dependent rows matter on their own; deletion should be a deliberate decision
RESTRICT	Same, and the check cannot be postponed	As above, strictly
CASCADE	The orders are deleted too	The child rows are part of the parent and meaningless without it
SET NULL	The orders keep existing, with customer_id set to NULL	The child can stand alone, and the column allows NULL

CASCADE is convenient and dangerous. We tried it on a scratch copy: a customer with two orders, ON DELETE CASCADE, one DELETE of the customer. Afterwards, zero orders were left, and nothing warned us.

Index the referencing column

Declaring orders.customer_id REFERENCES customers (id) does not create an index on orders.customer_id. The referenced side always has one, because it must be a primary key or unique. The referencing side is left to you.

That matters in two places:

• Deleting or changing a customer makes PostgreSQL look for orders that reference them. Without an index on orders.customer_id, that is a scan of the whole orders table, for every customer you delete.
• Joining orders to customers by customer, as in “this customer’s orders”, is the lookup you met in B-tree indexes.

On a large table you will usually want CREATE INDEX ON orders (customer_id);. The practice database leaves it out because the table is tiny, and that is a decision you should make deliberately, not by accident.

Check yourself

1. What is the default ON DELETE behavior, and what does it do when references remain?

   Show answer

   NO ACTION. The delete is refused with a foreign key violation error while referencing rows still exist.

   I could explain this without looking

2. When is ON DELETE CASCADE appropriate, and when is it dangerous?

   Show answer

   It is appropriate when the child rows are part of the parent and meaningless without it, such as order items of an order. It is dangerous for records that matter on their own, such as orders of a customer, because one delete silently removes them all.

   I could explain this without looking

3. Why consider an index on a foreign key column?

   Show answer

   PostgreSQL does not create one automatically. Without it, deleting or updating a referenced row has to scan the whole referencing table to look for references, and lookups such as “this customer’s orders” cannot use an index.

   I could explain this without looking