CODEWITHSUNDEEP
mysqlsqlforeign-keys
user193130
user193130

Reputation: 8227

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

Using MySQL 5.5 with InnoDB

Upvotes: 7

Views: 7020

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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 ;

Upvotes: 11

Related Questions