Hi Experts,
I need a help to identify the parent and child relationship of foreign keys
a is parent of b
b is parent of c
c is parent of d
Script
create table a(aid number primary key);
create table b(bid number primary key);
create table c(cid number primary key);
create table d(did number primary key);
alter table b ADD CONSTRAINT fk_b foreign key (bid) REFERENCES a (aid);
alter table c ADD CONSTRAINT fk_c foreign key (cid) REFERENCES b (bid);
alter table d ADD CONSTRAINT fk_d foreign key (did) REFERENCES c (cid);
sample query to get FK details
SELECT c_pk.table_name parent_table_name,a.table_name as Child_table
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
and c.r_owner='SYS'
and a.table_name='B'
Excpected output
Parent_Table_name Child_Table_name hierarchy_level hierarchy_diagram
a b 1 a\b
b c 2 a\b\c
c d 3 a\b\c\d