mysql 删除和外键约束
我正在从 MYSQL 的两个表中删除选定的行,这两个表都有外键。
DELETE d,b
FROM A as b
INNER JOIN B as d on b.bid=d.bid WHERE b.name LIKE '%xxxx%';
即使我试图从两个表中删除,MYSQL 也会抱怨外键:
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)
从两个表中删除的最佳解决方案是什么?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
更改此约束以使用 ON DELETE CASCADE - 这意味着如果删除一行,则所有“子”行也将自动删除。
当然,要小心使用 CASCADE——仅在必要时使用它。如果您对它过于热衷,并且不小心执行了适当的 DELETE,则最终可能会删除数据库的一半。 :)
请参阅有关外键约束的文档。
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.
我想我明白你想要做什么
如果你无法更改表结构,那么你可以使用 2 个语句,第一个语句使用子选择
从 B 中删除,其中出价 IN(从 A 中选择出价,其中名称如' %xxxx%');
从 A 中删除名称为 '%xxxx%' 的地方;
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%';