Query to find table relationship types.

Question

Using Oracle, is there any way I can execute a query to determine what relationship a specific table has with any other tables in my database? I'm fiddling with the all_constraints table as of now.

Answer 1

Yes, you can do this for example:

select p.table_name, 'is parent of ' rel, c.table_name
from   user_constraints p
join   user_constraints c on c.r_constraint_name = p.constraint_name
                         and c.r_owner = p.owner
where p.table_name = 'MYTABLE'    
union all
select c.table_name, 'is child of ' rel, p.table_name
from   user_constraints p
join   user_constraints c on c.r_constraint_name = p.constraint_name
                         and c.r_owner = p.owner
where c.table_name = 'MYTABLE' 

Answer 2

I think your best bet is trying to extract as much information as you can from the foreign key constraints.

Have a look at this article at Database Journal that explains foreign key data mining in detail.