Cascading deletes like ON DELETE CASCADE for a one time operation in MySQL

Question

Is there some sort of magical SQL statement to delete a row and all its dependents (linked by foreign key constraints) WITHOUT altering the table to add ON DELETE CASCADE or deleting each dependent row manually?

I am fantasizing something such as DELETE FROM `table_a` WHERE `id` = 1 ON DELETE CASCADE; but I can't seem to find anything to this effect in the doc @ http://dev.mysql.com/doc/refman/5.5/en/delete.html

• I don't want to ALTER the table to change the constraints for just a one time operation and then revert it back using another ALTER
• I don't want to execute something like DELETE FROM `table_b` WHERE `a_id` = 1; for each table containing a FK to table_a

Using MySQL 5.5 with InnoDB

Answer 1

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

You either write down DELETE statements to delete all the related rows in the related tables or you have defined foreign key constraints with ON DELETE CASCADE.

Note that - as long as there are no circular paths in the foreign key relationships - it is possible to use a single DELETE statement that deletes from multiple 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 ;