Find all Foreign Key errors with Oracle SET CONSTRAINTS ALL DEFERRED

Question

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.

The user fixes the offending data and runs again. then hits another FK issue and repeats the process.

What I really want is a list of ALL FKs in one go that would cause the commit to fail.

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.

Answer 1

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.

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.

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.

I think you have to write the code.

Answer 2

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.

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.

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.

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.

Answer 3

The set immediate answer worked fine for me.

Taking the A,B,C example from the other post:

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.

Thanks