mysql delete and foreign key constraint

Question

I'm deleting selected rows from both table in MYSQL, the two tables have foreign keys. delete d,b from A as b inner join B as d on b.bid=d.bid where b.name like '%xxxx%';
MYSQL complains about foreign keys even though I'm trying to delete from both tables:

Error: Cannot delete or update a parent row: a foreign key constraint fails (yyy/d, CONSTRAINT fk_d_bid FOREIGN KEY (bid) REFERENCES b (bid) ON DELETE NO ACTION ON UPDATE NO ACTION)

what's the best solution here to delete from both table?

Answer 1

Change this constraint to use ON DELETE CASCADE -- which means that if a row is deleted, then any "child" rows will be automatically deleted as well.

Of course take good care of using CASCADE -- only use it when necessary. If you're overzealous with it, and accidentally do a well-placed DELETE, it might end up deleting half of your database. :)

See documentation on foreign key constraints.

Answer 2

I think I see what you're trying to do

If you can't change the table structure, then you could use 2 statements, the first with a sub-select

delete from B where bid IN (select bid from A where name like '%xxxx%');

delete from A where name like '%xxxx%';