How to CASCADE a delete from a child table to the parent table?
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.
You seem to want to kill the whole family, without regard to remaining children. Run this instead of DELETE FROM child ...:
DELETE FROM parent pUSING child cWHERE p.parent_id = c.parent_idAND c.child_name='michael';Then everything works with your current design. If you insist on your original DELETE statement, you need a rule or a trigger. But that would be rather messy.
From your question and sql fiddle, are you sure that you want to delete the parent AND all children if one child is deleted? If so, then use this:
CREATE FUNCTION infanticide () RETURNS trigger AS $$BEGIN DELETE FROM parent WHERE parent_id = OLD.parent_id; RETURN NULL;END; $$ LANGUAGE plpgsql;CREATE TRIGGER dead_kids BEFORE DELETE ON TABLE child FOR EACH ROW EXECUTE infanticide();It is imperative that you RETURN NULL from the BEFORE DELETE trigger: you want the delete to fail. The reason is that you delete the parent and that deletion will cascade to the child table (with the proper settings on the parent table). If you then try to delete more children in the same statement you are trying to make the system work on inexistent data and throwing out a parent that is already gone.