0

I want to bulk insert explicit data into two different tables linked by a foreign key using a same sequence-generated value in both tables.

I'm trying to use a INSERT ALL instruction together with a WITH clause made of successive UNION ALL statements to achieve that.

As long as the with clause returns no more than 256 rows, everything works fine. As soon as I add another UNION ALL entry, I'm getting the following error :

ORA-02291: integrity constraint violated (MY_SCHEMA.FK_TABLE_B_TO_TABLE_A) - 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.

If I disable the foreign key constraint, then again everything works fine (even with more than 256 rows).

My instruction looks like this :

insert all 
  into MY_SCHEMA.TABLE_A (ID, COLUMN_1, COLUMN_2)
    values (MY_SCHEMA.MY_SEQUENCE.nextval, COLUMN_1, COLUMN_2)
  into MY_SCHEMA.TABLE_B (ID, COLUMN_3) 
    values (MY_SCHEMA.MY_SEQUENCE.nextval, COLUMN_3) 

  with input_data as (
    select 11 COLUMN_1, 12 COLUMN_2, 13 COLUMN_3
      UNION ALL
    select 21 COLUMN_1, 22 COLUMN_2, 23 COLUMN_3
      UNION ALL
      ...
      UNION ALL
    select 31 COLUMN_1, 32 COLUMN_2, 33 COLUMN_3

  )
  select * from input_data;

Is there some limitation on the amount of data that such an instruction can handle ? Or am I missing something else ?

I'm using Oracle 11g and SQLDeveloper.

Thomas Naskali
  • 551
  • 5
  • 19
  • You are missing something. The error is a data validation error saying that a foreign key condition is not being met. It has nothing to do with the size of the data. One possible culprit is that the additional rows you want to insert have duplicate values. – Gordon Linoff May 04 '16 at 10:48
  • Thank you for your input, how do you explain then that disabling the FK makes the error disappear (with all the data being correctly inserted I should have mentioned) ? – Thomas Naskali May 04 '16 at 10:51
  • So when you disable the FK and use the same union all, all (more than 256) rows are inserted and tables have the same IDs? – artm May 04 '16 at 11:05
  • Correct. Can it be that the insertion order is not deterministic ? – Thomas Naskali May 04 '16 at 11:07

1 Answers1

3

When using multitable insert then it is better to disable or even drop all constraints. It has some important limitations:

  • sequences should not be used
  • the order of insertion into table is not guarantied

So even if you insert perfectly valid data, it might fail on FK constraints. Anyway if you want bulk insert your data quickly you do not want to waste the time waiting for FK to be checked.

Update: possible duplicate of: https://dba.stackexchange.com/questions/23384/using-multi-table-insert-for-parent-and-child-table

The Oracle bug(2891576) is not fixed yet. Oracle offers workaround:

Solution(Doc ID 265826.1)

"The order of the tables into which Oracle inserts data is not determinate (guaranteed). Therefore, before issuing a multitable insert statement, you should defer any constraints and disable any triggers that depend on a particular table order for the multitable insert operation."

WORKAROUND:

  1. Disable the foreign key when run such MultiPath Inserts.
  2. Use DEFERRED CONSTRAINTS so the checkout happens only at Commit time.

About Deferred Constraints check Metalink Note:73647.1 "Deferred Constraints Example"

Community
  • 1
  • 1
ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • What do you mean by _sequences cannot be used_ ? I know they cannot be used in a subquery (see [Oracle official documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#i2095116)), but In my example I use sequences and it's a multitable insert... – Thomas Naskali May 04 '16 at 12:06
  • I also couldn't find anywhere in the [Official documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#i2095116) something about the execution order of the INTO clauses being undefined – Thomas Naskali May 04 '16 at 12:10
  • I think these restriction I read somewhere in Datawarehousing guide. Here are some other restrictions mentioned: http://allthingsoracle.com/multi-table-insert-statements-in-oracle/ – ibre5041 May 04 '16 at 12:19
  • thank you for your last edit, that's exactly what I was looking for ! – Thomas Naskali May 04 '16 at 13:15
  • By "sequences can not be used" I meant that you can hardly can use sequence's(nextval, curval) to insert the same ID into parent and child table. – ibre5041 May 05 '16 at 08:02
  • The documentation is misleading. Sequences can be used, and successive calls to `nextval` within the same statement will return the same value (which is exactly what I need the have the same ID in both tables). See [this answer](http://stackoverflow.com/a/17060038/4074057) for details. – Thomas Naskali May 06 '16 at 05:46
  • Was the bug ever fixed? I experience it in 11.2 – David Balažic Mar 16 '17 at 20:06
  • 1
    @David Balažic not fixed yet. (not even in 12r2) – ibre5041 Mar 17 '17 at 08:34