SQL 删除孤儿

Question

假设所有外键都有适当的约束，是否有一个简单的 SQL 语句来删除数据库中任何地方未引用的行？

像 delete from the_table 这样简单的事情，只需跳过带有子记录的任何行？

我试图避免手动循环表格或添加类似 where the_SK not in (a,b,c,d) 的内容。

Answer 1

您也许可以使用 10g 中包含错误日志记录的扩展 DELETE 语句。

首先使用 DBMS_ERRLOG 创建一个日志表（它只是原始表的副本，带有一些附加前缀列：ORA_ERR_MESG$, ..., ORA_ERR_TAG$）

execute dbms_errlog.create_error_log('parent', 'parent_errlog');

现在，您可以使用删除语句的 LOG ERRORS 子句来捕获具有现有完整性约束的所有行：

delete from parent
   log errors into parent_errlog ('holding-breath')
   reject limit unlimited;

在这种情况下，“holding-breath”注释将进入 ORA_ERR_TAG$ 列。

您可以在此处阅读完整文档。

如果父表很大并且您只想删除一些杂散行，那么您最终会得到一个 parent_errlog 表，该表本质上是您的父表的副本桌子。如果这不行，您将不得不做很长的路：

1. 直接引用子表（遵循托尼的解决方案），或者，
2. 循环遍历 PL/SQL 中的表并捕获任何异常（遵循混乱和鲍勃的解决方案）。

Answer 2

最简单的方法可能是编写一个应用程序或存储过程，尝试逐一删除表中的行，并简单地忽略由于外键约束而导致的失败。然后，所有不受外键约束的行都应该被删除。根据所需/可能的性能，这可能是一种选择。

Answer 3

不，显然你可以这样做（但我意识到你宁愿不这样做）：

delete parent
where  not exists (select null from child1 where child1.parent_id = parent.parent_id)
and    not exists (select null from child2 where child2.parent_id = parent.parent_id)
...
and    not exists (select null from childn where childn.parent_id = parent.parent_id);

Answer 4

一种方法是编写如下内容：

eForeign_key_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(eForeign_key_violation, -2292);

FOR aRow IN (SELECT primary_key_field FROM A_TABLE) LOOP
  BEGIN
    DELETE FROM A_TABLE A
    WHERE A.PRIMARY_KEY_FIELD = aRow.PRIMARY_KEY_FIELD;
  EXCEPTION
    WHEN eForeign_key_violation THEN
      NULL;  -- ignore the error
  END;
END LOOP;

如果存在子行，则 DELETE 将失败，并且不会删除任何行，并且您可以继续处理下一个键。

请注意，如果您的表很大，这可能需要相当长的时间。