Using Multi-table Insert for Parent and Child Table

Question

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?

Answer 1

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:

DROP TABLE T1;
CREATE TABLE T1 AS (SELECT 'a' c1, 0 c2, 0 c3 FROM dual WHERE 1=2);
INSERT ALL 
   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;
COMMIT;   
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.

Answer 2

Isaac,

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.