DELETE CASCADE on a child row delete parent?

Question

I have 3 tables. The last two tables have a foreign key to the first on the same field. Both foreign keys are set to UPDATE CASCADE, DELETE CASCADE. When I delete a child-row in the second table, the parent row in the first table remains unchanged. But when I delete a child-row in the third table, the first row in the table is deleted!

Foreign keys should not behave in the same way as both daughters?

Can you please put your solution here so that everyone can see it? — Rachcha, Nov 26 '12 at 13:05
There was **another** foreign key set to delete cascade. I was the bug :) — Cristoforo Cervino, Jan 22 '13 at 10:48

score 1 · Answer 1 · answered Nov 27 '12 at 11:07

My guess is that table 2 uses MyISAM tables (which do not support foreign keys) while the other two tables are using the InnoDB engine (which do support foreign keys).

From the documentation:

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must both be InnoDB tables. They must not be TEMPORARY tables.

DELETE CASCADE on a child row delete parent?

1 Answers1