When the a referential integrity constraint is defined on another column within the same table the following errors are observed in the target event log while Post is processing a DELETE:
Info 2011-05-11 14:10:48.702793 24313 4120696512 Poster exited with code=1, pid = 20490 (posting from siesa, queue fedra, to siesa)
Error 2011-05-11 14:10:48.665603 20490 4099451792 s:2 Poster: Unexpected Oracle error: ORA-02292: integrity constraint (UNOEE.FK_T368_T368_1) violated - child record found. (object name: "UNOEE"."T368_AF_MOVTO_ACTIVO_FIJO") (posting from siesa, queue fedra, to siesa) [module opo]
Notice 2011-05-11 14:10:48.658538 20490 4099451792 s:2 Poster: ORA-02292: integrity constraint (UNOEE.FK_T368_T368_1) violated - child record found. (posting from siesa, queue fedra, to siesa) [module osp]
Info 2011-05-11 14:10:42.521549 20490 4120696512 Poster launched, pid = 20490 (posting from siesa, queue fedra, to siesa)
When deleting a row with a column value, another row exists which is a child to this column value.
The problem is typically observed when the transaction comprises multiple DML and until the transaction is viewed in totality up to the COMMIT, the individual DMLs are bound to fail because of this referential integrity constraint defined with one column in a table referencing another column in the same table. Another scenario is that on source the constraint is DEFERRED whereas on target it is not. To get around the error, define the constraint as:
INITIALLY DEFERRED DEFERRABLE on the target table.
Here is an example:
The replicating table named table3 containing two columns c and d (where d references c) initially has the foreign key constraint defined in the default manner:
SQL> create table table3(c number primary key, d number);
SQL> alter table table3 add constraint table3_fk foreign key(d)
2 references table3(c);
Now, if a row is deleted with a parent key still referenced by an existing child column, the following errors will be observed in event log even though the there may be another DML in the same transaction that will remove the child record later on:
Error 2011-05-17 17:14:39.988495 17632 4 s:2 Poster: Unexpected Oracle error: ORA-02292: integrity constraint (SHA76.TABLE4_FK) violated - child record found. (object name: "SHA76"."TABLE4") (posting from syam, queue alvsupu07, to syam) [module opo]
Notice 2011-05-17 17:14:39.926860 17632 4 s:2 Poster: ORA-02292: integrity constraint (SHA76.TABLE4_FK) violated - child record found. (posting from syam, queue alvsupu07, to syam) [module osp]
To get around the problem, define the target table with following attributes:
SQL> create table table3(c number primary key, d number);
SQL> alter table table3 add constraint table3_fk foreign key(d)
2 references table3(c) initially deferred deferrable;
This will ensure that the constraint validity check is deferred until the end of the transaction (when COMMIT is encountered).
