Is it safe to use Oracle's multi-table insert statement to insert into a (foreign key constrained) parent and child table?

With minimal examples, I've found that it works as long as the parent table comes before the child table in the into list. Can I rely on this or should I make the constraint deferrable?

  • Are you talking about INSERT ALL?
    – Philᵀᴹ
    Aug 29, 2012 at 19:48
  • @Phil: Yes I am. Aug 29, 2012 at 19:50
  • What about two inserts? Why go the roundabout way with deferrable constraints when the simple method of multiple inserts has always worked? Aug 30, 2012 at 8:59
  • 1
    @Vincent sometimes it takes a fraction of the time to insert all - that may be Isaac's reason? Aug 30, 2012 at 14:06
  • @JackDouglas Performance is a good reason, but INSERT ALL has lots of limitations: no parallel execution, no insert in a view. This leads me to think that this statement is less optimized that standard INSERT statements, especially when inserting in multiple tables. Aug 30, 2012 at 14:24

2 Answers 2


No, you can't depend on this. SQL is declarative, not procedural, so within a statement you can't guarantee the order of execution. Since the entire INSERT ALL statement is considered a single statement (doc), you can't guarantee that one INSERT will be before another.

By definition an INSERT FIRST must execute the first INTO passing the evaluated conditions. We might expect INSERT ALL to behave similarly. This appears to be the case:

CREATE TABLE T1 AS (SELECT 'a' c1, 0 c2, 0 c3 FROM dual WHERE 1=2);
   WHEN mod(x,2)<>0 THEN INTO T1 VALUES ('a', x, mod(x,2)) 
   WHEN mod(x,2)=0 THEN INTO T1 VALUES ('b', x, mod(x,2)) 
   SELECT Level x FROM dual CONNECT BY Level <=20;
SELECT rowid, c1, c2, c3 FROM t1;

However, even though we can demonstrate a particular behavior on a particular platform/version/patchset still doesn't make this a guarantee.

Oracle-developer.net says it explicitly:

the conditions in an INSERT FIRST statement will be evaluated in order from top to bottom. Oracle makes no such guarantees with an INSERT ALL statement.

  • 1
    Oracle do actually consider this non-deterministic behaviour a bug (see My Oracle Support article 265826.1 for bug 2891576). However, given this was raised back in 2003 for version, I doubt there's much chance of this being fixed anytime soon. Aug 30, 2012 at 16:04
  • 1
    @Chris Saxon Excellent find. The report demonstrates a case in which modifying the size of an unrelated column affects which into is done first. As expected the workaround is to use DEFERRED CONSTRAINTS. Aug 30, 2012 at 17:42


You can not rely on this as oracle does not guarantee the order of INSERT. The correct way to do this is, as you mentioned, with the deferred foreign key constraint.

  • The order does not matter, as the constraints are checked after the SQL command is executed. Well theoretically as due to the bug mentioned above it does not work correctly. See an example using an INSERT ... SELECT here: sqlfiddle.com/#!15/7de0f (note it works with PostreSQL and Oracle, but not with MySQL or MS SQL) Mar 17, 2017 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.