ORA-02292 in target when a table has a self-referential FK (4237381)

ORA-02292 in target when a table has a self-referential FK

When a child table has a self-referential integrity constraint, DELETE operation in source could cause ORA-02292 error in target.

-- TARGET
-- event_log

Error    2017-06-28 14:20:34.329830 9194 1099897152 s:3 Table "SCOTT"."S_FK_TEST" out of sync for queue source at source rowid AAAfKiAAEAAAADVAAA: ORA-02292: integrity constraint (SCOTT.S_FK_TEST_HIREARCHY_FK) violated - child record found   (posting from source1, queue source, to source)

In case there is a DELETE operation removing all rows which has the same parent value in ONE transaction, Oracle defers constraints implicitly, eventually SRC doesn't have any constraint issue. However, SharePlex post breaks the one DELETE statement with multiple DELETE statements so the related constraint is enforced to the individual DELETE statement.

For this reason, post encounters a constraint violation error.

Let's go over the detail with simple test

1. DDL: SRC & TGT

DROP TABLE S_FK_TEST PURGE;
CREATE TABLE S_FK_TEST
( ID number
, parent_ID number null
, name varchar2(40) not null
, constraint S_FK_TEST_PK primary key (ID)
, constraint S_FK_TEST_HIREARCHY_FK foreign key (parent_ID)
    references S_FK_TEST(ID)
);

select TABLE_NAME,STATUS,DEFERRABLE,DEFERRED
from user_constraints
where CONSTRAINT_NAME ='S_FK_TEST_HIREARCHY_FK';

TABLE_NAME                     STATUS   DEFERRABLE     DEFERRED
------------------------------ -------- -------------- ---------
S_FK_TEST                      ENABLED  NOT DEFERRABLE IMMEDIATE


2. Initial data: SRC

insert into S_FK_TEST values (1, null, 'Boss1');
insert into S_FK_TEST values (2, null, 'Boss2');

insert into S_FK_TEST values (3, 1, 'Worker1');
insert into S_FK_TEST values (4, 1, 'Worker1');

insert into S_FK_TEST values (5, 2, 'Worker2');


commit;


        ID  PARENT_ID NAME
---------- ---------- --------------------------------------------------------------------------------
         1            Boss1
         2            Boss2
         3          1 Worker1
         4          1 Worker1
         5          2 Worker2

3. DELETE: SRC

DELETE FROM S_FK_TEST
WHERE ID = 1;
    
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.S_FK_TEST_HIREARCHY_FK) violated - child record found

DELETE FROM S_FK_TEST
WHERE name in ('Boss1','Worker1')
/

3 rows deleted.

SQL> commit;
Commit complete.

4. Error found: TGT

Error    2017-06-28 14:20:34.329830 9194 1099897152 s:3 Table "SCOTT"."S_FK_TEST" out of sync for queue source at source rowid AAAfKiAAEAAAADVAAA: ORA-02292: integrity constraint (SCOTT.S_FK_TEST_HIREARCHY_FK) violated - child record found   (posting from source1, queue source, to source)

-- Host (source) Sid (source1)

-- session 3, 1 error --

--
-- [1] Wed Jun 28 14:20:32 2017

-- redolog seq#/offset 18544/1091088
-- redolog timestamp 947859630 (06/28/17 14:20:30)
-- original rowid AAAfKiAAEAAAADVAAA
-- ORA-02292: integrity constraint (SCOTT.S_FK_TEST_HIREARCHY_FK) violated - child record found
-- CONSTRAINT VIOLATION
delete   from "SCOTT"."S_FK_TEST" t where rownum = 1  and "ID"= '01';

5. Solution

Make the constraint DEFERRABLE.
As a self-referential FK is already created, it cannot be changed to DEFERRABLE.
Drop the constraint and recreate with an option 'INITIALLY DEFERRED'.

INITIALLY IMMEDIATE(Default)
INITIALLY DEFERRED