René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

August 6, 2005: On identifying parent and child tables

Two tables can be brought into a parent-child relation ship through referential integrity. Let's assume that I have some tables and I want to find out which are the parent and which are the child tables.
As usual, I create some tables to demonstrate the procedure. Their names start with ri_ to be able to identify them further below. 
create table ri_parent (
  a number primary key
);

create table ri_child_1 (
  b number references ri_parent(a)
);

create table ri_child_2 (
  b number references ri_parent(a)
);
Now, let's find the child-parent relationships. A referential integrity condition puts one constraint (either a primary key or a unique key) on the parent table and an additional constraint on the child table (foreign key). These two constraints are exposed through the dba_constraints views. Having two of them (for each parent-child relation), I have to join dba_constraints against itself.
The foreign key constraints names the name of the parent's constraint in r_constraint_name 
set lines 140

select
  child.owner        || '.' ||
  child.table_name   "Child table",
  'is child of'      " ",
  parent.owner       || '.' ||
  parent.table_name  "Parent table"
from
  dba_constraints child join dba_constraints parent on
    child.r_constraint_name = parent.constraint_name and
    child.r_owner           = parent.owner
where
  parent.table_name like 'RI\_%' escape '\' --';

Child table                                                               Parent table
------------------------------------------------------------- ----------- --------------
RENE.RI_CHILD_1                                               is child of RENE.RI_PARENT
RENE.RI_CHILD_2                                               is child of RENE.RI_PARENT
Of course, I can also query the other way round: 
select
  parent.owner        || '.' ||
  parent.table_name   "Parent table",
  'is parent of'      " ",
  child.owner         || '.' ||
  child.table_name    "Child table"
from
  dba_constraints child join dba_constraints parent on 
    child.r_constraint_name = parent.constraint_name and
    child.r_owner           = parent.owner
where
  child.table_name like 'RI\_%' escape '\' --';

Parent table                                                               Child table
------------------------------------------------------------- ------------ ----------------------
RENE.RI_PARENT                                                is parent of RENE.RI_CHILD_1
RENE.RI_PARENT                                                is parent of RENE.RI_CHILD_2
Permalink: http://www.adp-gmbh.ch/blog/2005/august/6.html

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.