The Oracle docs state the following on the ora-02291 error:
ORA-02291: integrity constraint (%s.%s) violated - parent key not found"
Cause: A foreign key value has no matching primary key value
Oracle interprets this error as follows:
“ORA-2291 is reported even if the parent key is present in the reference table. This behavior is observed in 11gR1 while the same works fine on 10gR2 (10.2.0.4)”.
Test Case how to force ORA-02291
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 PRIMARYKEY);
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 ORA-2291:
Error starting at line : 33 in command - update aaa_child set parent_id='p0000000000000000002' where id='c0000000000000000002'
Error report - SQL Error: ORA-02291: integrity constraint (AE.AFK_CHILD_PID) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.
But as you can see the record exists in the parent table:
select * from aaa_parent;
ID STORE_ID
-------------------- --------------------
p0000000000000000001 s0000000000000000001
p0000000000000000002 s0000000000000000001