For the delete, I would implement the foreign keys with ON DELETE CASCADE and just delete the parent table.

For the inserts -- well, I cannot imagine a situation where I would not inheritly KNOW what the relationship where when inserting but ....

In 8.0 and later, i would use deferred constraints. In that fashion, I can insert in any order and the validation of the constraint is deferred until you commit. Since you have 7.2, that is not workable for you...

You should be aware that there are cases where you cannot get the right order. It is totally possible (common even) for A to have a foreign key to B and B to A. In this case -- the only solution is to insert NULL foreign keys and update them after the fact -- especially when putting that first row in there!

What might work for you is a query against user_constraints. We have to use a temporary table since the connect by won't work on the join. We can use a permanent temporary table if you surround the INSERT+OPEN Query with a savepoint and rollback to savepoint as I have below. That way, the temp table always appears to be empty.


ops$tkyte@ORA8I.WORLD> create table p ( pk int primary key );

Table created.

ops$tkyte@ORA8I.WORLD> create table c1( pk int primary key,
2 fk int references p );

Table created.

ops$tkyte@ORA8I.WORLD> create table c2( pk int primary key,
2 fk int references c1 );

Table created.

ops$tkyte@ORA8I.WORLD> create table c3( pk int primary key,
2 fk int references p );

Table created.

ops$tkyte@ORA8I.WORLD> create table c4 ( pk int primary key,
2 fk int references c3 );

Table created.

ops$tkyte@ORA8I.WORLD> create table c5( pk int primary key,
2 fk int references c4 );

Table created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> drop table temp;

Table dropped.

ops$tkyte@ORA8I.WORLD> create table temp ( child varchar2(30), parent varchar2(30) );

Table created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> savepoint foo;

Savepoint created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> insert into temp
2 select a.table_name child, b.table_name parent
3 from user_constraints a, user_constraints b
4 where a.constraint_type = 'R'
5 and a.r_constraint_name = b.constraint_name
6 /

6 rows created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select parent, rpad( '*', level, '*' ) || child child
2 from temp
3 start with parent = 'P'
4 connect by prior child = parent
5 /

PARENT CHILD
------------------------------ --------------------
P *C1
C1 **C2
P *C3
C3 **C4
C4 ***C5

ops$tkyte@ORA8I.WORLD> rollback to foo;

Rollback complete.

ops$tkyte@ORA8I.WORLD>