Foreign Keys and Multi-Table Inserts in Oracle

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.

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles