处理数据库完整性
我将在应用程序的下一版本中使用 innodb 约束引入数据库完整性。一切都很顺利，但我的一些表的记录已删除引用（死记录），因此我无法向表添加约束。
我正在尝试：
ALTER TABLE `article` ADD FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE;
我得到：
#1452 - Cannot add or update a child row: a foreign key constraint fails (`books`.<result 2 when explaining filename '#sql-442_dc'>, CONSTRAINT `#sql-442_dc_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE)
运行此查询，我发现超过 500 条记录没有引用（作者被删除，但他们的文章仍然存在）：
SELECT `articles`.`id`
FROM `articles` LEFT JOIN `authors` ON `articles`.`author_id` = `authors`.`id`
WHERE ISNULL(`authors`.`id`);
因此，在添加约束之前，我必须处理这些记录。 如何删除使用上面的查询获得的所有记录？
我已经尝试过：
DELETE FROM `articles` WHERE `id` IN (
SELECT `articles`.`id`
FROM `articles` LEFT JOIN `authors` ON `articles`.`author_id` = `authors`.`id`
WHERE ISNULL(`authors`.`id`);
)
但 mysql 回应：
You can't specify target table 'articles' for update in FROM clause
对此的任何帮助将不胜感激。
I'm introducing database integrity using innodb constraints in the next version of my application. Everything goes well, but some of my tables have records with deleted references (dead records) and because of them I can't add constraints to the table.
I am trying:
ALTER TABLE `article` ADD FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE;
And I get:
#1452 - Cannot add or update a child row: a foreign key constraint fails (`books`.<result 2 when explaining filename '#sql-442_dc'>, CONSTRAINT `#sql-442_dc_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE)
Running this query, I found out that over 500 records have no references (authors were deleted, but their articles remained):
SELECT `articles`.`id`
FROM `articles` LEFT JOIN `authors` ON `articles`.`author_id` = `authors`.`id`
WHERE ISNULL(`authors`.`id`);
So, before I can add a constraint, I must deal with those. How do I delete all the records that I get using the query above?
I've tried:
DELETE FROM `articles` WHERE `id` IN (
SELECT `articles`.`id`
FROM `articles` LEFT JOIN `authors` ON `articles`.`author_id` = `authors`.`id`
WHERE ISNULL(`authors`.`id`);
)
But mysql responds:
You can't specify target table 'articles' for update in FROM clause
Any help on this will be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我不太熟悉 mySql 的许多怪癖，但这应该也可以工作，也许 mySql 不会被它阻塞：
嗯，当然，在尝试基于集合的删除之前我们总是有表的备份:)
I'm not too familiar with mySql's many quirks, but this should also work, and perhaps mySql won't choke on it:
Um, of course we always have a backup of the table before we attempt set-based deletes :)