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 18.104.22.168.0 & SQL Developer 22.214.171.124. 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; CREATE SEQUENCE child_tbl_seq START WITH 500;
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 (the_pk, the_fk, object_name) VALUES (child_tbl_seq.nextval, 999, 'SomeObject');
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.
INSERT ALL INTO parent_tbl(the_pk, object_id) VALUES (parent_tbl_seq.nextval, object_id) INTO child_tbl(the_pk, the_fk, object_name) VALUES (child_tbl_seq.nextval, parent_tbl_seq.currval, object_name) SELECT uo.object_id, uo.object_name 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”
Thanks for this example. I was trying something like this
INTO parent_tbl(the_pk, object_id)
INTO child_tbl(the_pk, the_fk, object_name)
SELECT SYS_GUID() AS pk1, SYS_GUID() AS pk2,
‘data1′ p1,’data2’ c1
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?
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?
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?
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