使用 Oracle SET CONSTRAINTS ALL DEFERRED 查找所有外键错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/278871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find all Foreign Key errors with Oracle SET CONSTRAINTS ALL DEFERRED
提问by GHZ
I am using:
我在用：
set constraints all deferred;
(lots of deletes and inserts)
commit;
This works as expected. If there are any broken relationships then the commit fails and an error is raised listing ONE of the FKs that it fails on.
这按预期工作。如果存在任何断开的关系，则提交失败并引发错误，列出其失败的 FK 之一。
The user fixes the offending data and runs again. then hits another FK issue and repeats the process.
用户修复有问题的数据并再次运行。然后遇到另一个 FK 问题并重复该过程。
What I really want is a list of ALL FKs in one go that would cause the commit to fail.
我真正想要的是一次性列出所有会导致提交失败的 FK。
I can of course write something to check every FK relationship through a select statement (one select per FK), but the beauty of using the deferred session is that this is all handled for me.
我当然可以通过 select 语句（每个 FK 一个选择）编写一些东西来检查每个 FK 关系，但是使用延迟会话的美妙之处在于这一切都为我处理。
采纳答案by Gary Myers
First option, you can look into DML error logging. That way you leave your constraints active, then do the inserts, with the erroring rows going into error tables. You can find them there, fix them, re-insert the rows and delete them from the error table.
第一个选项，您可以查看DML 错误日志记录。这样您就可以保持约束处于活动状态，然后进行插入，将出错的行放入错误表中。您可以在那里找到它们，修复它们，重新插入行并从错误表中删除它们。
The second option is, rather than trying the commit, attempt to re-enable each deferred constraint dynamically, catching the errors. You'd be looking at a PL/SQL procedure that queries ALL_CONSTRAINTS for the deferrable ones, then does an EXECUTE IMMEDIATE to make that constraint immediate. That last bit would be in a block with an exception handler so you can catch the ones that fail, but continue on.
第二种选择是尝试动态重新启用每个延迟约束，而不是尝试提交，以捕获错误。您将查看一个 PL/SQL 过程，该过程查询 ALL_CONSTRAINTS 以获得可延迟的，然后执行 EXECUTE IMMEDIATE 以立即执行该约束。最后一点将在一个带有异常处理程序的块中，因此您可以捕获失败的那些，但继续。
Personally, I'd go for option (1) as you do get the individual records that fail, plus you don't have to worry about deferrable constraints and remembering to make them immediate again after this script so it doesn't break something later.
就我个人而言，我会选择选项 (1)，因为您确实得到了失败的单个记录，而且您不必担心可延迟约束，并记得在此脚本之后立即再次设置它们，以免以后破坏某些内容.
I guess somewhere in memory Oracle must be maintaining a list, at least of constraints that failed if not the actual rows. I'm pretty confident it doesn't rescan the whole set of tables at commit time to check all the constraints.
我想在内存中的某个地方，Oracle 必须维护一个列表，至少是如果不是实际行则失败的约束。我非常有信心它不会在提交时重新扫描整个表集来检查所有约束。
回答by GHZ
The set immediate answer worked fine for me.
设定的即时答案对我来说效果很好。
Taking the A,B,C example from the other post:
以另一篇文章中的 A、B、C 为例：
SQL> create table a (id number primary key);
Table created.
SQL> create table b (id number primary key, a_id number, constraint fk_b_to_a foreign key (a_id) references a deferrable initially immediate);
Table created.
SQL> create table c (id number primary key, b_id number, constraint fk_c_to_b foreign key (b_id) references b deferrable initially immediate);
Table created.
SQL> insert into a values (1);
1 row created.
SQL> insert into b values (1,1);
1 row created.
SQL> insert into c values (1,1);
1 row created.
SQL> commit;
Commit complete.
I have a consistent set of data.. my starting point. Now I start a session to update some of the data - which is what I was trying to describe in my post.
我有一组一致的数据......我的起点。现在我开始一个会话来更新一些数据——这就是我试图在我的帖子中描述的。
SQL> set constraints all deferred;
Constraint set.
SQL> delete from a;
1 row deleted.
SQL> delete from b;
1 row deleted.
SQL> insert into b values (10,10);
1 row created.
SQL> set constraint fk_b_to_a immediate;
set constraint fk_b_to_a immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (GW.FK_B_TO_A) violated - parent key not foun
SQL> set constraint fk_c_to_b immediate;
set constraint fk_c_to_b immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (GW.FK_C_TO_B) violated - parent key not foun
Which tells me about both broken constraints (C to B) and (B to A), without rolling back the transaction. Which is exactly what I wanted.
这告诉我两个破坏的约束（C 到 B）和（B 到 A），而不回滚事务。这正是我想要的。
Thanks
谢谢
回答by GHZ
Oracle's Error Mechanism is too primitive to return a collection of all errors that COULD occur. I mean, it's a cool thought but think about what you'd have to do if you wrote the code. Your standard error handling would need to be thwarted. Instead of returning an error as soon as you encounter it, you'd have to continue somehow to see if you could proceed if the first error you caught wasn't an error at all. And even if you did all of this you'd still face he possibility that the row you add that fixes the first error actually causes the second error.
Oracle 的错误机制过于原始，无法返回可能发生的所有错误的集合。我的意思是，这是一个很酷的想法，但想想如果你写了代码你必须做什么。您需要阻止标准错误处理。不要在遇到错误时立即返回错误，而是必须以某种方式继续以查看是否可以继续，如果您捕获的第一个错误根本不是错误。即使你做了所有这些，你仍然会面临你添加的修复第一个错误的行实际上导致第二个错误的可能性。
For example:
例如：
A is the parent of B is the parent of C. I defer and I insert C. I get an error that there's no B record parent. I defer and I add B. Now I get another error that there's no A.
A 是 B 的父级是 C 的父级。我推迟并插入 C。我收到一个错误，说没有 B 记录父级。我推迟并添加 B。 现在我收到另一个错误，即没有 A。
This is certainly possible and there's no way to tell in advance.
这当然是可能的，而且没有办法提前知道。
I think you're looking for the server to do some work that's really your responsibility. At the risk of mod -1, you're using a technique for firing an automatic weapon called "Spray and Pray" -- Fire as many rounds as possible and hope you kill the target. That approach just can't work here. You disable your RI and do a bunch of steps and hope that all the RI works out in the end when the database "grades" your DML.
我认为您正在寻找服务器来完成一些真正由您负责的工作。冒着 mod -1 的风险，您正在使用一种称为“Spray and Pray”的自动武器发射技术——尽可能多地发射子弹并希望杀死目标。这种方法在这里行不通。您禁用了您的 RI 并执行了一系列步骤，并希望当数据库对您的 DML 进行“评分”时，所有 RI 最终都能解决。
I think you have to write the code.
我认为您必须编写代码。
