13

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.

samol
  • 18,950
  • 32
  • 88
  • 127

3 Answers3

15

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • what I am worried about is not race condition. But instead, I am worried about a database crash before the trigger is executed. Would that mean that a parent record is not deleted? – samol Mar 18 '14 at 06:25
  • 1
    @alumns PostgreSQL is transactional, and triggers run within the transaction of the statement that fired them. So if the DB crashes, either the whole thing, or none of it, will be committed. The scenario you describe cannot happen. – Craig Ringer Mar 18 '14 at 06:36
  • You can also use a statement level DELETE TRIGGER: after the deletes have "committed" (in parenthesis because we are still within the same transaction block) on the child table delete any parents with 0 children. This would avoid the potential race condition. – Patrick Mar 18 '14 at 07:17
  • @Patrick Do you mean a deferred constraint trigger? If so, yes, that would work so long as there aren't any other deferred triggers that might conflict. – Craig Ringer Mar 18 '14 at 07:23
  • @CraigRinger I was actually referring to the issue of deletion of multiple children. Rather than check on every individual delete, do the check on the parent after all deletes have taken place, hence the statement level trigger. I suppose the use of a deferred trigger could also work. But on closer read, i do not see the race condition you are mentioning; the question was about DELETEs not INSERTs. Can you elaborate? – Patrick Mar 18 '14 at 07:38
  • @Patrick Imagine that one transaction is `DELETE`ing the last child, while a concurrent transaction is `INSERT`ing a new child with the same parent. Though come to think of it, I don't think there's a risk of any actual data issue, just an unexpected error message. – Craig Ringer Mar 18 '14 at 07:48
  • @CraigRinger See my alternative answer. Will that work? – Patrick Mar 18 '14 at 07:51
  • @Patrick: As I read the question (and the comments in the fiddle!), alumns wants to eradicate the whole family, no survivors. So, just kill the parent (like you do in your trigger ... just without a trigger, per request ...), the CASCADE option takes care of the rest. – Erwin Brandstetter Mar 18 '14 at 09:00
6

You seem to want to kill the whole family, without regard to remaining children. Run this instead of DELETE FROM child ...:

DELETE FROM parent p
USING  child c
WHERE  p.parent_id = c.parent_id
AND    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 will be rather messy.

DELETE statement in the manual.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I am trying to understand how this USING works. Is using equivalent to `JOIN` in `SELECT` ? – samol Mar 18 '14 at 17:16
  • @alumns: It's the keyword to join in additional tables in a `DELETE` statement. Since `FROM` was already taken (`DELETE FROM`) ... I added a link to the manual to my answer. – Erwin Brandstetter Mar 18 '14 at 20:28
1

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.

Patrick
  • 29,357
  • 6
  • 62
  • 90