Re: Integrity constraint

From: Jonathan Lewis <>
Date: Wed, 4 Feb 2009 15:46:57 -0000
Message-ID: <>

"vm22" <> wrote in message

Sorry Jon - ignore my last post.

It does seem like we are inserting while the delete is running. I am really surprised that Oracle does not ignore the new records that are added but thanks for the great explanation nevertheless.

What is the solution to this - I looked at the asktom but could not find anything.


The simplest change is to lock the Y and Z tables before you start the delete. But then the other processes will probably crash - on the other hand what would they do if they tried to insert data just AFTER your delete had completed ?

Since you're on 10.2 I thnk an alternative would be to use the "log errors" clause
with "reject limit unlimited". You need to precreate an error logging table with the dbms_errlog.create_error_log prcedure first, though. This would allow you to
identify the rows that had acquired new
child data between the start of the delete and the moment you got to the row.

I've just set up a little model on to see if my hypothesis was correct, and (provided the FK indexes are in place), I can cause the problem to appear on demand:

SQL> /
DELETE /*+ gather_plan_statistics */ FROM x *
ERROR at line 1:
ORA-02292: integrity constraint (TEST_USER.SYS_C0035144) violated - child record


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Wed Feb 04 2009 - 09:46:57 CST

Original text of this message