The idea for this article came from Tom Kyte’s answer to this Ask Tom question.
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. release 4.2 cause error when importing into 4.2 ORA-02291 14 Table 1 (Cont.).
Lines 2 – 4 insert into the parent table, note the use of the sequence to populate the primary key. The Oracle Application Express 4.2.4.00.08 patch set is a cumulative patch. APEX 4.2.4 with the environment at the time of the throw using SQL more. INTO child_tbl(the_pk, the_fk, object_name) The bug causes the same error like: error application to import 4.1 and 4.2. 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 In this video, we’ll explain what the ORA-02291 parent key not found error is, see an example, and look at how to resolve it.This error usually happens whe.
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, Oracle APEX JS Confirm and PL/SQL Dynamic Action By Andrew Schultz OctoNo Comments This blog is about how to use an Oracle APEX Dynamic Action to display a JavaScript (JS) confirm message and then run a subsequent PL/SQL block if confirmed. 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.