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> -- Create the main table
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> -- Dependent table
SQL> --
SQL> create table depend
2 (my_id number,
3 vchar_a varchar2(4000),
4 vchar_b varchar2(3920));
Table created.
SQL>
SQL> --
SQL> -- Add the foreign key constraint
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> -- Insert data into both tables with a
SQL> -- single statement
SQL> --
SQL> -- This could fail due to unresolved
SQL> -- bug 2891576
SQL> --
SQL> -- Since the order of the inserts is not
SQL> -- determinate (the first table in the
SQL> -- insert may not be the first table
SQL> -- processed by the insert) the child
SQL> -- table can be inserted into first, resulting
SQL> -- in an ORA-02291 being thrown
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> -- Create a useless trigger that does absolutely
SQL> -- nothing but fire
SQL> --
SQL> -- This can force Oracle to switch to row-level processing
SQL> -- and preserve the order of inserts
SQL> --
SQL> create or replace trigger pre_tbl_ins_trg
2 before insert on main
3 for each row
4 begin
5 --
6 -- Do absolutely nothing
7 --
8 null;
9 end;
10 /
Trigger created.
SQL>
SQL> --
SQL> -- This time the insert should succeed
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> -- Modified script originally written by
SQL> -- Jonathan Lewis
SQL> --
SQL> --
SQL> -- The failure can be random, as it depends upon
SQL> -- the order of the table inserts as Oracle generates
SQL> -- them
SQL> --
SQL>
SQL> --
SQL> -- Create table of source data
SQL> --
SQL> -- As it's a CTAS the segment will
SQL> -- be immediately created regardless
SQL> -- of the parameter setting
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> -- Create the parent table of the group
SQL> --
SQL> -- Since it is not being populated when created
SQL> -- the segment creation is declared as immediate
SQL> --
SQL> -- Should the user have no quota on the default
SQL> -- tablespace this create statement will fail
SQL> -- immediately
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> -- Create the dependent tables
SQL> --
SQL> -- Again the segment creation is declared
SQL> -- as immediate
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> -- Here we go
SQL> --
SQL> -- This multi-table insert will succeed or fail
SQL> -- depending upon the number of rows that will
SQL> -- be inserted
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> -- Here we go
SQL> --
SQL> -- This multi-table insert will succeed or fail
SQL> -- depending upon the number of rows that will
SQL> -- be inserted
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.
