View constraints in a hierarchy

To view a hierarchy of all constraints, starting with a Master table, use the following SQL statement;

select lpad(' ', 4 * level - 1 ) || sys_connect_by_path(Child, '/') as Hierarchy
from
  (
    select  ac.owner || '.' ||  ac.table_name as Child
           ,r.owner  || '.' ||  r.table_name  as Master
    from    all_constraints     ac
           ,all_constraints     r
    where   r.Owner             = ac.r_Owner
    and     r.Constraint_Name   = ac.r_Constraint_Name
    and     ac.Constraint_Type  = 'R'
)
start with Master =  sys_context('userenv','current_schema')||'.CLIENT'
connect by prior Child =  Master
/

For example, given the following set of tables and foreign keys;

create table product (p_id          number
                     ,product_name  varchar2(10)
                     ,price number);
alter table product add constraint p_pk primary key (p_id);

create table client  (c_id number
                     ,client_name varchar2(10));
alter table client add constraint c_pk primary key (c_id);

create table client_order (co_id  number
                          ,c_id   number
                          ,d      date);
alter table client_order add constraint co_pk primary key (co_id);

alter table client_order add constraint co_fk
foreign key (c_id) references client (c_id);

create table order_line (ol_id      number
                        ,co_id      number
                        ,p_id       number
                        ,qty        number
                        ,ol_amount  number);
alter table order_line add constraint ol_pk primary key (ol_id);

alter table order_line add constraint ol_cl_fk
foreign key (co_id) references client_order (co_id);

alter table order_line add constraint ol_po_fk
foreign key (p_id) references product (p_id);

Running the SQL against the CLIENT table will produce the following result;

SQL> variable cs varchar2(30)

SQL> exec :cs := sys_context('userenv','current_schema');

PL/SQL procedure successfully completed.

SQL> select    lpad(' ', 4 * level - 1 )

  2         || sys_connect_by_path(Child, '/') as Hierarchy

  3  from

  4    (

  5      select  ac.owner || '.' ||  ac.table_name as Child

  6             ,r.owner  || '.' ||  r.table_name  as Master

  7      from    all_constraints     ac

  8             ,all_constraints     r

  9      where   r.Owner             = ac.r_Owner

 10      and     r.Constraint_Name   = ac.r_Constraint_Name

 11      and     ac.Constraint_Type  = 'R'

 12  )

 13  start with Master=:cs||'.CLIENT'

 14  connect by prior Child =  Master;

HIERARCHY

------------------------------------------------------------

   /XDB_DEV4.CLIENT_ORDER

       /XDB_DEV4.CLIENT_ORDER/XDB_DEV4.ORDER_LINE

The following query lists the path leading to each table and the level at which the set of foreign key s exists within the path.

with t as
(
    select  Ref_Level
           ,Path
           ,substr(Path
                   ,instr(Path, '/', 1,Ref_Level) +1
                   , length(Path) ) as Table_Name
    from
    (
    select distinct
           level as ref_level
          ,lpad(' ', 4 * level - 1 ) 
               || sys_connect_by_path(Master, '/') as Path
    from
      (
        select  ac.table_name       as Child
               ,r.table_name        as Master
        from   
                all_constraints     ac
               ,all_constraints     r
        where  
                r.Owner             = ac.r_Owner
        and     r.Owner             = '&SCHEMA'
        and     r.Constraint_Name   = ac.r_Constraint_Name
        and     ac.Constraint_Type  = 'R'
    )
    connect by nocycle prior Child = Master
    )
)
--
select *
from
(
select
        Ref_Level
       ,nvl(first_value(Ref_Level) 
        over (partition by table_name 
              order by rn), Ref_Level) as last_level
       ,Path
       ,Table_Name
       ,rn
from
    (
    select     
            Ref_Level
           ,Path 
           ,Table_Name
           ,row_number() 
            over (partition by table_name 
                  order by Ref_Level desc) as rn
    from
            t
    )
);

Listing all foreign key constraints

select ac.owner
      ,ac.table_name
      ,r.owner        as ref_owner
      ,r.table_name   as ref_table
from   all_constraints ac
      ,all_constraints r 
where  r.owner            = ac.r_owner
and    r.constraint_name  = ac.r_constraint_name
and    ac.constraint_type = 'R'
/