Using INSERT ALL with related tables

In this article I will demonstrate a method where the INSERTALL statement can be used to insert rows into two tables that are related by a foreign key constraint.

The following example was developed and tested using the Pre-Built Oracle Database App Development VM which at the time of this post used Oracle Database  & SQL Developer  The example was created within the standard HR schema.

First I create two tables:

CREATE TABLE parent_tbl (the_pk    NUMBER PRIMARY KEY,
                         object_id NUMBER);

CREATE TABLE child_tbl (the_pk      NUMBER PRIMARY KEY,
                        the_fk      NUMBER,
                        object_name VARCHAR2(30));

Next I add a foreign to define the relationship between these tables and two sequences used to populate the primary keys:

ALTER TABLE child_tbl
ADD CONSTRAINT child_tbl_fk1 FOREIGN KEY (the_fk)
   REFERENCES parent_tbl (the_pk);

CREATE SEQUENCE parent_tbl_seq;


Next I ensure that the foreign key relationship is working as expected by trying to insert a record into the child table with value for the_fk column that doesn’t exist in parent_tbl:

INSERT INTO child_tbl

Attempting to run this statement results in the expected error message:

SQL Error: ORA-02291: integrity constraint (HR.CHILD_TBL_FK1) violated - parent key not found

With the tables and relationship between them in place I can now demostrate how to use INSERTALL to insert information from user_objects into the parent and child tables.

INTO parent_tbl(the_pk, object_id) 
VALUES (parent_tbl_seq.nextval, 
INTO child_tbl(the_pk, the_fk, object_name) 
VALUES (child_tbl_seq.nextval,
SELECT uo.object_id,
FROM user_objects uo

Lines 2 – 4 insert into the parent table, note the use of the sequence to populate the primary key.

Lines 5 – 8 insert into the child table, the important part (and the focus of this article) is to understand how the foreign key column, the_fk is populated. It uses currval of the sequence that was used to populate the parent table.

Thanks go to Tom Kyte for coming up with this elegant solution.


In this article I have demonstrated how to use INSERTALL to insert into two tables that are related by a foreign key.


The idea for this article came from Tom Kyte’s answer to this Ask Tom question.

4 thoughts on “Using INSERT ALL with related tables

  1. Thanks for this example. I was trying something like this

    INTO parent_tbl(the_pk, object_id)
    VALUES (pk1,
    INTO child_tbl(the_pk, the_fk, object_name)
    VALUES (pk2,
    SELECT SYS_GUID() AS pk1, SYS_GUID() AS pk2,
    ‘data1′ p1,’data2’ c1
    FROM dual

    It throws error ‘Parent Not found’

    I tested by removing Foreign Key constraint. It worked but all different ids. It looks like it select each row again to insert into each table. Thats why I got all ids different.

    Any work around?

    1. When you insert into the child table you need to know what the parent id for that row is. Which is straight forward to achieve with the sequence pseudo columns currval and nextval. Why are you using SYS_GUID for the primary keys and not a sequence?

  2. Is this code thread safe? Will parent_tbl_seq.currval return the generated ID for this transaction or will pick up the next one generated for anohter parallel insert?

    1. Thanks for the question, the short answer is I don’t know. If you want to put together a proof of concept and let me know what you find out I would be happy to add it to the article crediting you of course. Regards Ian

Leave a Reply to oraclefrontovik Cancel reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.