It also contains information that a PL/SQL programmer frequently uses to write stored procedures. The idea for this article came from Tom Kyte’s answer to this Ask Tom question. This 'cheat sheet' covers most of the basic functionality that an Oracle DBA needs to run basic queries and perform basic tasks.
In this article I have demonstrated how to use INSERTALL to insert into two tables that are related by a foreign key. Thanks go to Tom Kyte for coming up with this elegant solution. It uses currval of the sequence that was used to populate the parent table. 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. Oracle Database lets you create six types of constraints and lets you declare them in two ways. Lines 2 – 4 insert into the parent table, note the use of the sequence to populate the primary key. Use a constraint to define an integrity constraint- a rule that restricts the values in a database. INTO child_tbl(the_pk, the_fk, object_name)
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. 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:Īttempting 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 Next I add a foreign to define the relationship between these tables and two sequences used to populate the primary keys:ĪDD CONSTRAINT child_tbl_fk1 FOREIGN KEY (the_fk)ĬREATE SEQUENCE child_tbl_seq START WITH 500 The example was created within the standard HR schema.ĬREATE TABLE parent_tbl (the_pk NUMBER PRIMARY KEY,ĬREATE TABLE child_tbl (the_pk NUMBER PRIMARY KEY, 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 12.1.0.2.0 & SQL Developer 4.0.3.16. 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.