user193130 user193130 - 2 months ago 12
MySQL Question

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

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

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 ;
Comments