MySQL Multi-Delete. Is it possible to multi-delete

Question

If I have a parent table and a child table, is it possible to multi-delete the rows in them without having a "ON DELETE CASCADE" constraint?

In this example:

create table a(id int primary key);
create table b(id int primary key, a_id int,
 constraint fkb foreign key (a_id) references a(id));

Is it not possible to do something like this in order to delete rows in tables a and b? :-(

delete a, b
from b
inner join a on a.id = b.a_id
where a.id = ?;

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails
(`erasmusu6`.`b`, CONSTRAINT `fkb` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`))

I would like to multidelete rows but not to set a "ON DELETE CASCADE" constraint. Also I need to filter the DELETE command with a WHERE clause. Is this possible or should I have to make as many DELETEs as tables in the multidelete?

Answer 1

This is the note from mysql documentation page (http://dev.mysql.com/doc/refman/5.0/en/delete.html):

"If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly."

So, this implies that you are forced not to use multi delete option!

Hope that helps..

Answer 2

I solve the problem with optimizer hints, by specifying the exact join order in the DELETE command:

delete a, b
from b
STRAIGHT_JOIN a on a.id = b.a_id
where a.id = ?;

MySQL will DELETE b rows first thanks to the optimizer hint STRAIGHT_JOIN.