处理数据库完整性 [英] Handling database integrity

问题描述

我在下一个版本的应用程序中使用innodb约束来引入数据库完整性。一切都很顺利，但我的一些表有记录删除引用（死记录），因为他们我不能添加约束到表。

：
$ b $ pre $ ALTER TABLE`article`添加外键（`author_id`）参考`authors`（`id`）ON DELETE CASCADE ;


我得到：

 ＃1452  - 无法添加或更新子行：外键约束失败（`books`。& lt;结果2解释文件名时'#sql-442_dc'& gt ;, CONSTRAINT` ＃sql-442_dc_ibfk_1` FOREIGN KEY（`author_id`）参考`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` author```````author_id` =`author`.`id` 
 WHERE ISNULL（`authors`.`id`）; 
）
  

但是mysql响应：

 在FROM子句
中指定目标表'articles'作为更新。
 
 
 $ b 
任何帮助都将不胜感激。  
 
解决方案我不太熟悉mySql的许多怪癖，但是这也应该起作用，也许mySql不会窒息它： 
 
 
 从文章中删除
不存在的地方（
从作者$ b中选择id $ b其中authors.id = articles.author_id 
）
  
嗯，当然我们总是有一个备份之前我们尝试基于集合的删除：）
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. 
解决方案
I'm not too familiar with mySql's many quirks, but this should also work, and perhaps mySql won't choke on it:
delete from articles
 where not exists (
           select id from authors
            where authors.id = articles.author_id
       )
Um, of course we always have a backup of the table before we attempt set-based deletes :)

                        
这篇关于处理数据库完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！