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?
Best Answer
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 oneINSERT
will be before another.By definition an
INSERT FIRST
must execute the firstINTO
passing the evaluated conditions. We might expectINSERT ALL
to behave similarly. This appears to be the case: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: