Cascading deletion, e.g. ON DELETE CASCADE for a single operation in MySQL

Question

Is there any magic SQL statement for deleting a row and all its dependents (bound by foreign key constraints) WITHOUT modifying the table to add ON DELETE CASCADEor deleting each dependent row manually?

I fantasize something like DELETE FROM `table_a` WHERE `id` = 1 ON DELETE CASCADE;, but I can't find anything like it in doc @ http://dev.mysql.com/doc/refman/5.5/en/delete.html

• I do not want the table to ALTERchange the restrictions on only a single operation, and then return it back using anotherALTER
• I don’t want to do something like DELETE FROM `table_b` WHERE `a_id` = 1;for every table containing FK beforetable_a

Using MySQL 5.5 with InnoDB

Answer 1

No, the simple answer is: no, there is no shortcut.

You either write statements DELETEto delete all related rows in related tables, or you define foreign key constraints with ON DELETE CASCADE.

Note that - if there are no circular paths in the foreign key relationship, you can use one statement DELETEthat removes from several tables:

DELETE a, b, c, d
FROM a
  LEFT JOIN b  ON  b.a_id = a.a_id
  LEFT JOIN c  ON  c.a_id = a.a_id
  LEFT JOIN d  ON  d.b_id = b.b_id 
WHERE
    a.a_id = 1 ;