asked on

List of parent and child tables hierarchy

Dear Oracle Gurus,

I am trying to write a oracle query to display all the parent and child tables in a schema based on Primary key , foreign key constraints.
In some cases a particular parent table (which has multiple child tables) will be a child table to another table itself. My query output should look like:

Parent      Child      Level

T1        T2
T1        T3
T1        T4
T1        T5
T6        T7
T6        T8
T10        T9
T10        T11
T12        T1
T12        T10
T12 T13

I am trying to solve a situation where for me to delete data in Table 12, I need to first delete T9 , T11 and then T10. So looking for additional columns in the query result that will tell me the order in which data needs to be deleted.

Regards
Lavanya

slightwv (䄆 Netminder)

I would see if the constraints are set up to delete cascade. If so, you only need to delete from the parent and the child rows will automatically be deleted.

There is a DELETE_RULE in dba_constraints/all_constraints/user_constraints that tell you if they cascade or not.

Even if not, you still might be able to delete from a join statement is all the foreign keys are set up properly (I've never tried this):
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6254523200346736598

About the question asked:
>>write a oracle query to display all the parent and child tables in a schema based on Primary key , foreign key constraints
There are many of these queries already written.

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

--
-- Addenda: Oct 2018
-- Thanks to Stew Ashtom for this update
--

with pur as (
  select table_name, constraint_type, constraint_name, r_constraint_name,
    max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
  from user_constraints 
  where constraint_type in ('P', 'U', 'R')
)
, son_dad as (
  select distinct s.table_name son, d.table_name dad, d.constraint_type
  from (select * from pur where constraint_type = 'R' or is_r = 0) s
  left join pur d
    on s.r_constraint_name = d.constraint_name
    and s.table_name != d.table_name
)
select level lvl, son, dad, constraint_type
from son_dad
start with dad is null
connect by dad = prior son
order siblings by dad, son;

Open in new window

>>So looking for additional columns in the query result

I don't know what you mean by this.

johnsone

That is a great query. I saved that one off.

The only thing that I can see is that I don't think it would account for a constraint that crossed schemas. I don't think those occur in many places, but I don't believe that this would find one of those.

I did something very similar, and I don't recall the method I used, I was trying to recreate it. Before you were able to do these kinds of queries and I think it was in SQL Server, but the method would hold on any database. It had to do with counting the number of times a table was referenced, then account for tables that referenced each other at the same counts and things like that. The looping was pretty tough, but it worked. This is way easier.

If anyone wants to play with it, I generated the create statements for the original question:

create table t1 (id number, primary key(id));
create table t2 (id number, primary key(id));
alter table t2 add foreign key (id) references t1(id);
create table t3 (id number, primary key(id));
alter table t3 add foreign key (id) references t1(id);
create table t4 (id number, primary key(id));
alter table t4 add foreign key (id) references t1(id);
create table t5 (id number, primary key(id));
alter table t5 add foreign key (id) references t1(id);
create table t6 (id number, primary key(id));
create table t7 (id number, primary key(id));
alter table t7 add foreign key (id) references t6(id);
create table t8 (id number, primary key(id));
alter table t8 add foreign key (id) references t6(id);
create table t10 (id number, primary key(id));
create table t9 (id number, primary key(id));
alter table t9 add foreign key (id) references t10(id);
create table t11 (id number, primary key(id));
alter table t11 add foreign key (id) references t10(id);
create table t12 (id number, primary key(id));
alter table t1 add foreign key (id) references t12(id);
alter table t10 add foreign key (id) references t12(id);
create table t13 (id number, primary key(id));
alter table t13 add foreign key (id) references t10(id);

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.