Hi Team,
We have many tables (master tables) having primary and foreign key relationships. These tables (normalized) contain static data (master data).
Inserting data manually in these tables is a tedious task because if we insert data out of order then we are bound to get constraint error.
Can we automate this process of data insertion? Like if we insert data in one de-normalized table and plsql procedure will read this table and insert the data in proper order i.e. first in Parent table and then in child tables.
Table relationships:
(Parent Table) (Child Table) (Sub-Child Table)
A1 B1 C1
A2 B2
A3 C1
... ....
You could look at deferrable constraints here. Here's an example
SQL> create table t1 ( c1 int primary key, c2 int);
Table created.
SQL> create table t2 ( c2 int primary key, c1 int);
Table created.
SQL>
SQL> alter table t1 add constraint fk1 foreign key ( c2 ) references t2 ( c2 );
Table altered.
SQL> alter table t2 add constraint fk2 foreign key ( c1 ) references t1 ( c1 );
Table altered.
Now no matter what we do here, we're stuck, because each table refers to the other
SQL>
SQL> insert into t1 values (1,1);
insert into t1 values (1,1)
*
ERROR at line 1:
ORA-02291: integrity constraint (MCDONAC.FK1) violated - parent key not found
SQL> insert into t2 values (1,1);
insert into t2 values (1,1)
*
ERROR at line 1:
ORA-02291: integrity constraint (MCDONAC.FK2) violated - parent key not found
So we'll repeat it now with deferrable constraints
SQL> drop table t1 cascade constraints purge;
Table dropped.
SQL> drop table t2 cascade constraints purge;
Table dropped.
SQL> create table t1 ( c1 int primary key, c2 int);
Table created.
SQL> create table t2 ( c2 int primary key, c1 int);
Table created.
SQL>
SQL> alter table t1 add constraint fk1 foreign key ( c2 ) references t2 ( c2 ) deferrable;
Table altered.
SQL> alter table t2 add constraint fk2 foreign key ( c1 ) references t1 ( c1 ) deferrable;
Table altered.
SQL>
SQL> set constraint all deferred;
Constraint set.
SQL> insert into t1 values (1,1);
1 row created.
SQL> insert into t2 values (1,1);
1 row created.
SQL> commit;
Commit complete.
But I stress - deferrable constraints are there to get you out of situations like the one above, where enforcing constraints immediately is not possible or difficult. It's not a replacement for not understanding the data model, or not having good control over your application code.
There are implications to consider - some of which are covered here:
https://asktom.oracle.com/Misc/oramag/on-deferring-and-bulking-up.html