Intermittently receive ORA-02291 error when parent key exists

Document ID : KB000084356
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Error Message :
U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-02291: integrity constraint (UC4.FK_RH_AH) violated - parent key not found'

Even though an existing parent key exists, intermittently receive error messages similar to those below indicating that the parent key is not found.

20160929/224207.562 - 20160913/045018.817 - U00003621 Version of Automation Engine database objects: '11.2.1+hf.1.build.440'
20160929/224207.565 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-02291: integrity constraint (UC4.FK_RH_AH) violated - parent key not found'

Investigation
  •  In the server log files normally located in the temp directory of the Automation Engine you will see the ORA-02291 error.
  •  Definition of an ORA-02291 error from the Oracle documentation.
ORA-02291: integrity constraint (%s.%s) violated - parent key not found"
Cause: A foreign key value has no matching primary key value
 
An ORA-2291 is reported even if the parent key is present in the referenced table.  This behavior is observed in 11gR1 while the same works fine on 10gR2 (10.2.0.4).
  • The database statement which raises this error is also available in the log file:
INSERT INTO RH (RH_Client, RH_AH_Idnr, RH_Type, RH_TimeStamp1, RH_DeleteFlag, RH_ArchiveFlag, RH_BlkSize, RH_Title, RH_XMLFlag, RH_FileOnAgent, RH_FileInDB, RH_UsersLogin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Test Case to force an ORA-02291 Error
 
drop table aaa_parent cascade constraints;
drop table aaa_child cascade constraints;
 
create table aaa_parent(id CHAR(20) not null CONSTRAINT apk_parent_id PRIMARY KEY);
 
create table aaa_child (id CHAR(20) not null, parent_id CHAR(20) not null);
 
alter table aaa_child add constraint afk_child_pid FOREIGN KEY (parent_id)references aaa_parent (id);
 
insert into aaa_parent (id) values ('p0000000000000000001');
insert into aaa_parent (id) values ('p0000000000000000002');
insert into aaa_child (id, parent_id)values('c0000000000000000001','p0000000000000000001');
insert into aaa_child (id, parent_id) values('c0000000000000000002','p0000000000000000002');
commit;
 
alter table aaa_parent add(store_id char(20) default 's0000000000000000001' not null);
alter table aaa_child add(store_id char(20) default 's0000000000000000001' not null);
 
alter table aaa_child drop constraint afk_child_pid drop index;
alter table aaa_parent drop constraint apk_parent_id drop index;
 
ALTER TABLE aaa_parent ADD CONSTRAINT apk_parent_id PRIMARY KEY (id,store_id);

ALTER TABLE aaa_child ADD CONSTRAINT afk_child_pid FOREIGN KEY (parent_id,store_id) REFERENCES aaa_parent (id, store_id);
 
update aaa_child set parent_id='p0000000000000000002' where id='c0000000000000000002';
 
The last update statement will fail with an ORA-02291 error.
0EMb0000001QVTA.png
But as you can see the record exists in the parent table:
 
select * from aaa_parent;

0EMb0000001QVTU.png
Please see the attached log file for this test case. 
 
Conclusions
 
If an ORA-02291 error can be reproduced using the above test case outside of the Automation Engine, this leads us to believe that this is related to an Oracle defect and not likely a defect in the Automation Engine.  However, it is possible that other circumstances could cause the behavior and in those cases Automic would have to further investigate the issue.
 
References
  
Cause:
Cause type:
Other
Root Cause: Oracle defect
Resolution:
We suggest disabling the constraint and routinely verifying that records exist in the child table without a matching record in the parent table.

Fix Status: No Fix

Additional Information:
Workaround :
N/A
File Attachments:
TestCase.txt