You Asked

How can I randomly generate and insert an "x" number of rows into a parent table (x being independent of the size of the parent and children tables), where it (the parent table) might have multiple FK columns from two or more child tables?

For instance, if there is Parent table which has, among other columns, two FK's from Child_One and Child_Two tables, how can I, for each row that I want to insert into Parent, randomly insert FK's that actually exist in Child_One and Child_Two into Parent?

The insertion must be done in one go given those FK's are NOT NULL in Parent table, so they cannot be inserted separately.

and Connor said...

How about using a fixed seed value to dbms_random to generate the same set of random numbers repeatedly ? eg


SQL> drop table child cascade constraints purge;

Table dropped.

SQL> drop table parent cascade constraints purge;

Table dropped.

SQL>
SQL> create table parent ( x int primary key );

Table created.

SQL> create table child ( c int, x int references parent ( x ));

Table created.

SQL>
SQL> exec dbms_random.seed(0);

PL/SQL procedure successfully completed.

SQL>
SQL> insert into parent
  2  with random_set as
  3  ( select distinct ceil(dbms_random.value(0,1000)) r1
  4    from dual
  5    connect by level <= 100
  6  )
  7  select r1 from random_set;

96 rows created.

SQL>
SQL> exec dbms_random.seed(0);

PL/SQL procedure successfully completed.

SQL>
SQL> insert into child
  2  with random_set as
  3  ( select distinct ceil(dbms_random.value(0,1000)) r1
  4    from dual
  5    connect by level <= 100
  6  )
  7  select rownum,r1 from random_set;

96 rows created.

SQL>
SQL>
SQL> select * from parent;

         X
----------
         7
        23
        32
        48
        52
        60
        64
        67
        70
        90
        93
        97
       113
       121
       123
       128
       129
       133
       159
       162
       164
       170
       174
       185
       219
       225
       232
       244
       252
       270
       298
       328
       341
       346
       362
       371
       380
       381
       400
       403
       455
       461
       468
       471
       489
       495
       516
       531
       553
       563
       583
       600
       612
       614
       622
       635
       652
       658
       659
       672
       676
       690
       702
       714
       722
       724
       752
       763
       764
       772
       792
       803
       817
       818
       819
       824
       829
       835
       841
       857
       861
       864
       878
       895
       897
       913
       920
       927
       936
       941
       945
       951
       954
       955
       962
       997

96 rows selected.

SQL> select * from child;

         C          X
---------- ----------
         1        455
         2        614
         3        129
         4        403
         5        380
         6        123
         7        672
         8        400
         9        861
        10        468
        11        244
        12        878
        13        724
        14        658
        15        128
        16        121
        17        252
        18        113
        19        895
        20        920
        21        652
        22        371
        23         70
        24        461
        25        763
        26         90
        27        185
        28        817
        29        941
        30        841
        31        897
        32        298
        33        489
        34        635
        35        600
        36        962
        37        829
        38        945
        39        270
        40        495
        41        162
        42         32
        43        702
        44        133
        45        764
        46        622
        47        583
        48        164
        49        232
        50        954
        51         23
        52        328
        53        553
        54        516
        55         48
        56        857
        57        471
        58        381
        59         93
        60        362
        61        341
        62        772
        63        563
        64         52
        65        792
        66        913
        67        659
        68         64
        69        819
        70        824
        71        936
        72        722
        73        951
        74          7
        75        864
        76         97
        77        927
        78        612
        79        835
        80        955
        81        714
        82        225
        83        219
        84        346
        85        174
        86        752
        87        997
        88        818
        89        676
        90        531
        91         60
        92        170
        93        690
        94        803
        95         67
        96        159

96 rows selected.

SQL>
SQL>

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Other Option | MultiTable insert

Rajeshwaran Jeyabal, August 29, 2016 - 7:11 am UTC

demo@ORA11G> exec dbms_random.seed(0);

PL/SQL procedure successfully completed.

demo@ORA11G> insert all
  2     when (r1 is not null) then into parent(x) values(r1)
  3     when (r1 is not null) then into child(c,x) values(rn,r1)
  4  with random_set as
  5  (select distinct ceil(dbms_random.value(0,1000)) r1
  6   from dual
  7   connect by level <=100 )
  8  select r1,rownum rn from random_set  ;

192 rows created.

demo@ORA11G> select count(*) from parent ;

  COUNT(*)
----------
        96

1 row selected.

demo@ORA11G> select count(*) from child ;

  COUNT(*)
----------
        96

1 row selected.

demo@ORA11G>

August 29, 2016 - 12:15 pm UTC

multi-table insert does *not* guarantee order of execution, so you might insert a child before the parent and get a constraint error.

Other Option | MultiTable insert

Rajeshwaran Jeyabal, August 29, 2016 - 1:37 pm UTC

Each row returned by the with clause subquery "in this case" will be unique.

....
multi-table insert does *not* guarantee order of execution
....

then are you saying that oracle will process the "When" condition in different order that it is been coded ?

August 30, 2016 - 1:20 am UTC

Some examples here

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6915127515933

In particular the comment:

"We had the same problem and found that it was a known bug. Here is an excerpt from Note 265826.1 on Metalink:

...
CAUSE

This issue is an open bug:
<bug:2891576> MULTI TABLE INSERT (INSERT ALL) FAILS WITH ORA-2291
Based on the above:
"The order of the tables into which Oracle inserts data is not determinate. Therefore, before issuing a multitable insert statement, you should defer any constraints and disable any triggers that depend on a particular table order for the multitable insert operation."

FIX

WORKAROUND:
1. Disable the foreign key when run such MultiPath Inserts.
2. Use DEFERRED CONSTRAINTS so the checkout happens only at Commit time.
About Deferred Constraints check Metalink <Note:73647.1> "Deferred Constraints Example"
...

"

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Other Option | MultiTable insert

Other Option | MultiTable insert

More to Explore

PL/SQL demos

PL/SQL docs

How to Insert Random PKs from Child Tables Into Parent Table's FKs

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Other Option | MultiTable insert

Other Option | MultiTable insert

More to Explore

PL/SQL demos

PL/SQL docs