I have a requirement so as to delete records from tables in order according to
the existing foreign keys.
For example, i have the following tables and the pk, fk constraints:
create table a
(aa number(1),
descr varchar2(20));
alter table a
add constraint a_pk primary key(aa);
create table b
(aa number(1),
descr varchar2(20));
alter table b
add constraint b_pk primary key(aa);
create table c
(aa number(1),
descr varchar2(20));
alter table c
add constraint c_pk primary key(aa);
create table a2
(aa number(2),
id_aa number(1),
descr varchar2(20));
alter table a2
add constraint a2_pk primary key(aa);
alter table a2
add constraint a2_fk foreign key(id_aa)
references a(aa);
create table b2
(aa number(2),
id_aa number(1),
descr varchar2(20));
alter table b2
add constraint b2_pk primary key(aa);
alter table b2
add constraint b2_fk foreign key(id_aa)
references b(aa);
create table z
(aa number(3),
id_aa number(1),
id_bb number(1),
descr varchar(20));
alter table z
add constraint z_pk primary key(aa);
alter table z
add constraint z_fk1 foreign key(id_aa)
references a(aa);
alter table z
add constraint z_fk2 foreign key(id_bb)
references b(aa);
So, i want to select the table names in such an order so as
the record deletion will succeed....
I have constructed the following sql query (using recursive subquery factoring):
[p]
with q(table_name, constraint_name, r_constraint_name, lvl) as
(select table_name, constraint_name, r_constraint_name, 1 lvl
from user_constraints a
where a.constraint_type = 'P'
union all
select b.table_name , b.constraint_name, b.r_constraint_name, q.lvl+1 lvl
from user_constraints b
join q
on (q.constraint_name = b.r_constraint_name)
where b.constraint_type = 'R'
)
select f.table_name, f.constraint_name, f.r_constraint_name, f.lvl
from q f
[/p]
I want the results as the following list:
Table_name
----------------
B2
A2
Z
A
B
C
The tables - B2, A2, Z- (in whichever order) should be reffered in the list first
because they are based on the other three tables - A, B, C. So, in order to delete the
A, B, C table records the B2, A2, Z table records must be beleted first.
The query i posted above has the problem that it displays the tables A2, B2 twice
(1. because they do have a pk and 2.because they have fk reffering the A, B tables relatively).
Is there any solution for this problem?
Note: I use db11g v2
I didn't write all relations to tables user_constraints (only the constraint_name= r_constraint_name)
Thanks,
Sim