How to CASCADE a delete from a child table to the parent table?

Question

I prepared a fiddle which demonstrates the problem.

CREATE TABLE parent (
   parent_id integer primary key
);

CREATE TABLE child (
   child_name TEXT primary key,
   parent_id integer REFERENCES parent (parent_id) ON DELETE CASCADE
);

INSERT INTO parent VALUES (1);
INSERT INTO child VALUES ('michael',1), ('vanessa', 1);

I want a way for the delete to CASCADE to the parent record when a child record is deleted.
For example:

DELETE FROM child WHERE child_name='michael';

This should cascade to the parent table and remove the record.

Answer 1

Foreign keys only work in the other direction: cascade deletes from parent to child, so when the parent (referenced) record is deleted, any child (referencing) records are also deleted.

If it's a 1:1 relationship you can create a bi-directional foreign key relationship, where one side is DEFERRABLE INITIALLY DEFERRED, and both sides are cascade.

Otherwise, you will want an ON DELETE ... FOR EACH ROW trigger on the child table that removes the parent row if there are no remaining children. It's potentially prone to race conditions with concurrent INSERTs; you'll need to SELECT ... FOR UPDATE the parent record, then check for other child records. Foreign key checks on insert take a FOR SHARE lock on the referenced (parent) record, so that should prevent any race condition.