In what order are ON DELETE CASCADE constraints processed?

Question

Here is an example of what I've got going on:

CREATE TABLE Parent (id BIGINT NOT NULL,
  PRIMARY KEY (id)) ENGINE=InnoDB;

CREATE TABLE Child (id BIGINT NOT NULL,
  parentid BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY (parentid),
  CONSTRAINT fk_parent FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE) ENGINE=InnoDB;

CREATE TABLE Uncle (id BIGINT NOT NULL,
  parentid BIGINT NOT NULL,
  childid BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY (parentid),
  KEY (childid),
  CONSTRAINT fk_parent_u FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE,
  CONSTRAINT fk_child FOREIGN KEY (childid) REFERENCES Child (id)) ENGINE=InnoDB;

Notice there is no ON DELETE CASCADE for the Uncle-Child relationship; i.e. deleting a Child does not delete its Uncle(s) and vice-versa.

When I have a Parent and an Uncle with the same Child, and I delete the Parent, it seems like InnoDB should be able to just "figure it out" and let the cascade ripple through the whole family (i.e. deleting the Parent deletes the Uncle and the Child as well). However, instead, I get the following:

  ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cascade_test/uncle`, CONSTRAINT `fk_child` FOREIGN KEY (`childid`) REFERENCES `child` (`id`))

InnoDB is trying to cascade-delete the Child before the Uncle(s) that refer to it.

Am I missing something? Is this supposed to fail for some reason I don't understand? Or is there some trick to making it work (or is it a bug in MySQL)?

Answer 1

In the simpler case, what happens if a record is deleted from Child and it has a referencing Uncle? That's unspecified, so the constraints fail for that anyway.

If deleting a Child does not delete its Uncles, then what happens instead? Uncle.childid cannot be null.

What you want is one of these three things:

1. Uncle.childid can be null, and you want ON DELETE SET NULL for childid.
2. Uncle.childid cannot be null, and you want ON DELETE CASCADE for childid.
3. Childid does not belong on Uncle, and you want a ChildsUncle relation with ON DELETE CASCADE foreign key constraints to both Child and Uncle. Uncleid would be a candidate key for that relation (i.e. it should be unique).

Answer 2

the design is all wrong. You should have single table, with parent child relationship (literrally). Then you can figure out uncles (and aunts) with a query

select id from persons where -find all children of the grandparents
parent id in (
select parentid from persons --find the grandparents
where id in (
select parentid from persons --find the parents
where id=THECHILD) )
minus --and take out the child's parents
select parentid from persons
where id=THECHILD


Answer 3

@apocalisp

Thanks. You're correct that if just the Child is deleted, and it has an Uncle, then the delete should fail (and does). However, when the Parent is deleted, it seems like InnoDB should be able to clean up everything properly.

However, I just did the same test with PostgreSQL and it failed in the same way. So maybe I'm just missing some fundamental concept.

Here's the PostgreSQL error message:

# delete from parent where id = 1;

ERROR:  update or delete on table "child" violates foreign key constraint "fk_child" on table "uncle"
DETAIL:  Key (id)=(1) is still referenced from table "uncle".
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."child" WHERE "parentid" = $1"

Answer 4

The parent deletion is triggering the child deletion as you stated and I don't know why it goes to the child table before the uncle table. I imagine you would have to look at the dbms code to know for sure, but im sure there is an algorithm that picks which tables to cascade to first.

The system does not really 'figure out' stuff the way you imply here and it is just following its constraint rules. The problem is the schema you created in that it encounters a constraint that will not let it pass further.

I see what you are saying.. if it hit the uncle table first it would delete the record and then delete the child (and not hit the uncle cascade from the child deletion). But even so, I don't think a schema would be set up to rely on that kind of behavior in reality. I think the only way to know for sure what is going on is to look through the code or get one of the mysql/postgresql programmers in here to say how it processes fk constraints.