Thanks for the question, Ren�.

Asked: August 07, 2017 - 8:32 am UTC

Last updated: August 16, 2017 - 1:32 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello Oracle Masters,

I'm trying to retrieve the column-table dependencies using the primary key/foreign key relations of a given table, but with another table as stop clause. I've been trying my luck with the recursive subquery factoring method mentioned here https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5822332300346315797, but unfortunately without much success :(

Say I'm having the tables and test values of the linked LiveSQL Scheme, my goal would be to traverse all dependencies till I reach table A.
Precondition: All tables are in one way or another related to table A. Some are in direct parent-child relationships, others are in mapping tables.

Then I'd only want to see the path that lead me there (the reason for that will be explained later on).

Now when executing statement #33, instead of the achieved result I would have looked for something like this (first three cols omited):

LVL TABLE_NAME1 COLUMN_NAME1 TABLE_NAME2 COLUMN_NAME2
1 C C_B_ID B B_ID
2 B B_A_ID A A_ID

Unfortunately I'm not able to get that far.

Same goes for statement #34, with the mapping not making it easier, I'd like the result to be like this:

LVL TABLE_NAME1 COLUMN_NAME1 TABLE_NAME2 COLUMN_NAME2
1 D D_ID E E_D_ID
2 E E_A_ID A A_ID

Later on I plan to create dynamic update statements, using the results as my where clause, because the update will be on the A_ID value.

Is something like I'm looking for actually possible using the recursive subquery factoring method or did I use the wrong approach?

Hope you can help me out =)

Kind regards,
René

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/fdxizmocf6qa3wkz2eyoyqy54

and Chris said...

OK. So part of the problem here is when you have a chain of tables C -> B -> A.

When you follow the FK from C to B you get to B_PK. To then go onto A, you need to join back to the constraints on B to find its FKs.

So really your selects in the recursive with need to get both the source constraint (PK or FK) and the target (FK or PK). I've also included the columns in the recursive query.

This gives a much more complex base query along the lines of:

select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')

And a similar one in the recursive branch. But rather than joining on constraint names, join user_constraints on the target table.

To stop processing when you get to A, include a check that the destination table isn't A. This gives a query like:

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as (
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')
  union all
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         lev + 1, 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         path || ',' || ucc2.table_name path
  from   constr c 
  join user_constraints uc
  on     uc.table_name = c.dst_tab
  and    uc.constraint_type in ('R', 'P')
  and    uc.table_name <> 'A'
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
) search depth first by src_tab set tab_order
  cycle cyc_tab set cycle to 1 default 0
  select c.*, 
         case when lev < lead(lev) over (order by tab_order) then null
           else path
         end is_leaf
  from   constr c
  where  cycle = 0;

This will then work up and down the relationships. So if you start at a table with children that don't lead to A (such as B), you'll end up with extra rows:

SRC     DST     SRC_TAB  DST_TAB  LEV  TP  COL1    COL2    CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  
B_A_FK  A_PK    B        A        0    R   B_A_ID  A_ID    A        A     1          0      A        
B_PK    C_B_FK  B        C        0    P   B_ID    C_B_ID  B        C     2          0      C

To knock these out you need to check that your path has A as a leaf.

I've done this by returning the destination table if the current row is a leaf. Then "filling up" this leaf using last_value, walking back up the tree.

You then return just the rows where the result of this is A:

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as (
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')
  union all
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         lev + 1, 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         path || ',' || ucc2.table_name path
  from   constr c 
  join user_constraints uc
  on     uc.table_name = c.dst_tab
  and    uc.constraint_type in ('R', 'P')
  and    uc.table_name <> 'A'
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
) search depth first by src_tab set tab_order
  cycle cyc_tab set cycle to 1 default 0
  select c.*, 
         case when lev < lead(lev) over (order by tab_order) then null
           else path
         end is_leaf
  from   constr c
  where  cycle = 0;

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as (
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')
  union all
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         lev + 1, 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         path || ',' || ucc2.table_name path
  from   constr c 
  join user_constraints uc
  on     uc.table_name = c.dst_tab
  and    uc.constraint_type in ('R', 'P')
  and    uc.table_name <> 'A'
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
) search depth first by src_tab set tab_order
  cycle cyc_tab set cycle to 1 default 0, 
  leaves as (
  select c.*, 
         case when lev < lead(lev) over (order by tab_order) then null
           else dst_tab
         end is_leaf
  from   constr c
  where  cycle = 0
), rng as (
  select l.*,
         last_value(is_leaf) ignore nulls over (order by tab_order desc) lv
  from   leaves l
)
  select * from rng
  where  lv = 'A'
  order  by lev;

Which gives the following results:

-- for D
SRC     DST     SRC_TAB  DST_TAB  LEV  TP  COL1    COL2    CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  LV  
D_PK    E_D_FK  D        E        0    P   D_ID    E_D_ID  D        E     1          0               A   
E_A_FK  A_PK    E        A        1    R   E_A_ID  A_ID    A        E,A   2          0      A        A 

-- for C
SRC     DST   SRC_TAB  DST_TAB  LEV  TP  COL1    COL2  CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  LV  
C_B_FK  B_PK  C        B        0    R   C_B_ID  B_ID  B        B     1          0               A   
B_A_FK  A_PK  B        A        1    R   B_A_ID  A_ID  A        B,A   2          0      A        A   

-- for B
SRC     DST   SRC_TAB  DST_TAB  LEV  TP  COL1    COL2  CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  LV  
B_A_FK  A_PK  B        A        0    R   B_A_ID  A_ID  A        A     1          0      A        A

For an explanation of how to find leaves using recursive with, read: http://www.orafaq.com/node/2996

Note this includes a couple of assumptions which you may need to tweak:

- You have no cross-schema constraints. If you do, you'll need to use all* views and include the owner in tables
- All FKs point to PKs with none pointing to unique constraints
- You have no composite PKs (or at least, none with FKs referencing them!)

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

René, August 16, 2017 - 8:37 am UTC

Hello,

thank you so much for that awesome solution, that's exactly what I was looking for (was driving me insane a bit :)).

I just had to add a minor tweak due to a changed table relation, where X -> D and A_X_D now nullable, which caused the relationships to be like this:
E -> A
E -> D
A -> X -> D

which in turn would have lead to the following result when searching for table D:

SRC DST SRC_TAB DST_TAB LEV TP COL1 COL2 CYC_TAB PATH TAB_ORDER CYCLE IS_LEAF LV
D_PK X_D_FK D X 0 P D_ID X_D_ID D X 4 0  -  A
D_PK E_D_FK D E 0 P D_ID E_D_ID D E 1 0  -  A
X_PK A_X_FK X A 1 P X_ID A_X_ID A X,A 6 0 A A
E_A_FK A_PK E A 1 R E_A_ID A_ID A E,A 2 0 A A

But due to to a_x_id now being nullable and me not needing any relation A-> table, the path D -> X -> A would not have worked out in later proceedings, so I changed

uc.constraint_type in ('R','P')

uc.constraint_type = 'R'

at one point (Statement #19).
I also tried checking for nullable columns in Statement #20, which also seems to work out fine, both leading to the wanted result

SRC DST SRC_TAB DST_TAB LEV TP COL1 COL2 CYC_TAB PATH TAB_ORDER CYCLE IS_LEAF LV
D_PK E_D_FK D E 0 P D_ID E_D_ID D E 1 0  -  A
E_A_FK A_PK E A 1 R E_A_ID A_ID A E,A 2 0 A A

Of course someone might also be inclined to instead of ignoring nullable FKs to select the path where FKs are nullable only when there is no other path, where the FKs are all not nullable, available. But fortunately I don't need that for my usecase :)

The changed table relation and tweaked solution is available here https://livesql.oracle.com/apex/livesql/s/ffd4wykf9013tr361r2yyxnof

Once again thank you so much for the solution, kind regards
René

August 16, 2017 - 1:32 pm UTC

Thanks, it took me a while to get to the bottom of this too ;)

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

Design

How to retrieve bidirectional parent-child dependencies of a given table

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

Design