-1

I have a question about generating data to databese. Generally I don't have problem with it but I don't know how to generate correct foreign keys. Example: I have three tables: Factory, Worker and Product. Table Product has two foreign keys: to Factory and to Worker and table Worker has primary key to Worker and foreign key to Factory so if I generated data to Product first I have links between Factory and Worker so how I can genereta date to Worker now?

MT0
  • 143,790
  • 11
  • 59
  • 117
BlueMan
  • 27
  • 5
  • Are you trying to find a way to get the id of the last inserted record so you can apply that to the next insert? It is not clear what exactly your problem is exactly. – Igor May 17 '21 at 17:45
  • Why last inserted record? If you have two tables or three with the same keys you can't generate random data to the second because in first you created links between keys. – BlueMan May 17 '21 at 18:48

2 Answers2

0

If foreign keys are enabled, then you must insert parent records first, child next.

SQL> create table factory
  2    (id number primary key);

Table created.

SQL> create table worker
  2    (id      number primary key,
  3     id_fact number references factory
  4    );

Table created.

SQL> create table product
  2    (id      number primary key,
  3     id_fact number references factory,
  4     id_work number references worker
  5    );

Table created.

SQL>
SQL> insert into factory values (1);

1 row created.

SQL> insert into worker values (100, 1);

1 row created.

SQL> insert into product values (1000, 1, 100);

1 row created.

SQL>

You can't - as you said - insert values into product first because parent row(s) doesn't exist yet:

SQL> rollback;

Rollback complete.

SQL> insert into product values (1000, 1, 100);
insert into product values (1000, 1, 100)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C007766) violated - parent key not
found


SQL>

If you created foreign key constraints to be deferrable, then you can insert rows in any order you want as Oracle will check integrity on commit:

SQL> create table factory
  2    (id number primary key);

Table created.

SQL> create table worker
  2    (id      number primary key,
  3     id_fact number references factory initially deferred deferrable
  4    );

Table created.

SQL> create table product
  2    (id      number primary key,
  3     id_fact number references factory initially deferred deferrable,
  4     id_work number references worker  initially deferred deferrable
  5    );

Table created.

SQL> insert into product values (1000, 1, 100);

1 row created.

SQL> insert into factory values (1);

1 row created.

SQL> insert into worker values (100, 1);

1 row created.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Unfortunately that dont solve my problem. This prevent from inserting data with incorrect keys but I want to generate random data. If you have two tables or three with the same keys you can't generate random data to the second because in first you created links between keys. – BlueMan May 17 '21 at 18:52
  • 1
    Of course you can't generate random data. You must satisfy certain conditions, and that's to maintain referential integrity. Therefore, if you follow the rules, you are out of the "random" sphere. – Littlefoot May 17 '21 at 19:35
0

You need to generate data for the tables in the correct order: if a table is referenced by an FK then it needs to be populated before the table that holds the FK.

So you would need to generate data in the order:

  1. Factory
  2. Worker
  3. Product
NickW
  • 8,430
  • 2
  • 6
  • 19