An interesting issue reared its head back in version 9.2 of Oracle and, unfortunately, has not been corrected. The problem involves inserting data into multiple tables with a single statement when a foreign key is declared. Regardless of the table order in the insert statement, the following error can be thrown:
ORA-02291: integrity constraint (SCOTT.FK1) violated - parent key not found
The error is generated because the order of iusert is not determinate. This means that regardless of the order of the tables in the code, inserts are not guaranteed to occur in the table listed first in the statement before the table listed second. To add fuel to the confusion fire, the error isn’t always thrown as it can depend on the database block size and the number of rows inserted. An example can help to proves this.
A simple single-column table, named MAIN, is created and it contains a primary key:
SQL>
SQL>
SQL>
SQL> create table main
2 (my_id number,
3 constraint main_pk primary key (my_id));
Table created.
SQL>
Another table, named DEPEND, is created with a foreign key constraint referencing MAIN:
SQL>
SQL>
SQL>
SQL> create table depend
2 (my_id number,
3 vchar_a varchar2(4000),
4 vchar_b varchar2(3920));
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> alter table depend add constraint fk1 foreign key (my_id) references main (my_id);
Table altered.
SQL>
Using the multi-table insert syntax an attempt is made to insert data into both tables with care being taken to list the MAIN table first in the statement. Unfortunately due to bug 2891576 this doesn’t happen:
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert all
2 into main ( my_id ) values ( my_id )
3 into depend ( my_id ) values ( my_id )
4 select 666 my_id from dual;
insert all
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK1) violated - parent key not found
SQL>
Oracle is attempting to insert data into DEPEND first, and since it has the foreign key constraint the insert fails due to a missing parent. One way around this is to create a basically useless trigger; it’s ‘basically’ useless as it does absolutely nothing to the data. What it does, apparently, is shift Oracle into row-level processing mode, which seems to force ‘proper’ table ordering for the insert:
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger pre_tbl_ins_trg
2 before insert on main
3 for each row
4 begin
5
6
7
8 null;
9 end;
10 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> insert all
2 into main ( my_id ) values ( my_id )
3 into depend ( my_id ) values ( my_id )
4 select 100 my_id from dual;
2 rows created.
SQL>
As if by magic the inserts now are successful.
A different example, that may be more ‘real world’, follows. The script is adapted from work by Jonathan Lewis and shows how ‘random’ the error can appear. The example is run on a database with an 8K block size. It’s possible that a database with a larger block size (16K or 32k) could increase the number of rows inserted before throwing the error. Three tables are created for this example, not counting the source table that is initially created. [TAB_1 is used to populate the remaining tables using a multi-table insert … select statement.] As the test tables are not poupulated segment creation is declared as immediate. This ensures that the empty tables are physically created in the database. The insert is run three times, with varying numbers of rows to insert. We begin:
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create table tab_1
2 as
3 with data_source as (
4 select rownum my_id
5 from dual
6 connect by level <= 1e4
7 )
8 select rownum my_id,
9 lpad(rownum,10,'0') vchar,
10 lpad(rownum,100,'0') vchar_med,
11 lpad(rownum,200,'0') vchar_lrg
12 from data_source v1;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create table prim_tab(
2 my_id number,
3 vchar varchar2(10),
4 vchar_med varchar2(100),
5 vchar_lrg varchar2(200),
6 constraint ptab_pk primary key(my_id)
7 )
8 segment creation immediate;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create table dep_tab(
2 my_id number,
3 vchar varchar2(10),
4 vchar_med varchar2(100),
5 vchar_lrg varchar2(200),
6 constraint chld_pk primary key(my_id),
7 constraint chld_fk_par foreign key (my_id) references prim_tab(my_id)
8 )
9 segment creation immediate;
Table created.
SQL>
SQL> create table dep_tab2(
2 my_id number,
3 vchar varchar2(10),
4 vchar_med varchar2(100),
5 vchar_lrg varchar2(200),
6 constraint ch2_pk primary key(my_id),
7 constraint ch2_fk_par foreign key (my_id) references prim_tab(my_id)
8 )
9 segment creation immediate;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert all
2 into prim_tab(my_id, vchar) values(my_id, vchar)
3 into dep_tab (my_id, vchar_med) values(my_id, vchar_med)
4 into dep_tab2(my_id, vchar_med) values(my_id, vchar_med)
5 select my_id, vchar, vchar_med, vchar_lrg
6 from tab_1
7 where rownum <= &num_rows;
Enter value for num_rows: 55
old 7: where rownum <= &num_rows
new 7: where rownum <= 55
165 rows created.
SQL>
For an 8K block a bulk insert of 55 rows behaves as expected for all three tables. Try again, this time with a 77-row bulk insert:
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert all
2 into prim_tab(my_id, vchar) values(my_id, vchar)
3 into dep_tab (my_id, vchar_med) values(my_id, vchar_med)
4 into dep_tab2(my_id, vchar_med) values(my_id, vchar_med)
5 select my_id, vchar, vchar_med, vchar_lrg
6 from tab_1
7 where rownum <= &num_rows;
Enter value for num_rows: 77
old 7: where rownum <= &num_rows
new 7: where rownum <= 77
insert all
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CHLD_FK_PAR) violated - parent key not
found
For the block size in use for this database (8K) the bulk limit is 75 rows; any larger batch will throw the error shown above.
Oracle has two bugs listed for this same issue, 2891576 and 16133798. Bug 16133798 is listed as “Closed: not a bug”, yet bug 2891576 is not listed as resolved nor is there a version listed where this problem is fixed.
The issue still exists in version 19.2, so it’s likely that it could appear unexpectedly in any release from Oracle 9.2 onward. As illustrated two possible workarounds are available – create a ‘do-nothing’ trigger to force Oracle into row-level processing or restrict insert batch sizes to 75 rows or fewer (again, this could be a higher number in databases with block sizes larger than 8K). In either case knowing the issue exists may help developers write code that avoids using a multi-table insert.
