mysql delete and foreign key constraint
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 tr开发者_如何转开发ying 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?
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.
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%';
精彩评论