Skip to Main Content
  • Questions
  • Which row violates deferred foreign key?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Volker.

Asked: February 10, 2016 - 2:49 pm UTC

Last updated: February 10, 2016 - 4:26 pm UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Viewed 1000+ times

You Asked

Hi Tom!

Is there a way to learn, after the fact mind you, which row caused a foreign key violation on commit? I understand DML logging only works for non-deferred constraints.

Sincerely
Volker

and Chris said...

You're right, your constraints have to be immediate for DML error logging to catch them.

I'm not aware of a way for you to capture the invalid rows on commit.

What you could do is:

- Before committing, setting the fk(s) to immediate (set constraint .. immediate)
- If this throws an exception, run a query to find the invalid rows

create table t1 (x primary key) as
  select rownum x from dual connect by level <= 10;
 
create table t2 (
  t1x int constraint fk references
    t1 (x) deferrable initially deferred
);

insert into t2 values (0);

set constraint fk immediate;

SQL Error: ORA-02291: integrity constraint (CHRIS.FK) violated - parent key not found

select * from t2
where  not exists (
  select * from t1
  where  x = t1x
);

       T1X
----------
         0

You'll need to update your code to do this and capture the invalid rows and log them. You can't find this information out "after the fact".

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

At least some closure

Volker Bach, February 10, 2016 - 4:28 pm UTC

Perfect, now at least I know I can stop searching for an automatic solution, thank you!