Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Parent child relationship oracle - Struggling to pass with the solutions found

Hi, I have been trying to figure out the order for the attached list of tables. I have got the list by querying dba constraints tables (PARENT_TABLE, TABLE, CHILD TABLE). But, I need to order them as "Table_name" ==> Table_LEVEL.
Example : deal-1
scenario -1
deal_address-2 etc.
Thanks much for looking into my problem.
  • 2
  • 2
1 Solution
how did you get 3 levels of tables from dba_constraints?  the foreign keys only have 2 levels

also, you have circular references  such as


are you sure your data is correct? and can you describe what the output is supposed to be using a smaller set?
maybe 5 rows of data and what you expect the output to be for those 5 rows?
I understand this:  while constraints only have 2 levels, each table potentially has a level above it and a level below it.  In other words, the three column list comes from a self join of the constraint table to itself.  

To calculate the level is a recursive operation and I don't think there is any way to do it in Oracle SQL.  You can however, write a recursive procedure that starts from any constraint and climbs up the hierarchy counting parents until no more are found.  However, do note that a table can have more than one constraint and consequently a different level for each one.   In cases where I need to know the level order (such as the correct order to process a series of deletes in a hierarchy (ilel child-up) without violating RI, using the max level of each table has always worked.  

I do agree that you sample query looks suspiciously wrong: the circular references being one example, stipulation_status as a child of stipulation being another.

sarran_vAuthor Commented:
When there are cyclic relationships (DEAL/SCENARIO or STIPULATION/STIPULATION_STATUS), One of the reference columns is null. In these cases, I can disable the constraints and do the operations and enable them. But, I'm not allowed to do the same for all tables.

This is what I used to populate the above xls.

   V_TBL_NM    VARCHAR2 (50);
   FOR I
      IN (SELECT dt.table_name
            FROM dba_tables dt
           WHERE dt.owner = 'DEAL'
                 AND table_name NOT IN
                        (SELECT table_name FROM grand_parent_tbls
                         SELECT table_name FROM grand_child_tbls)
                 AND TABLE_NAME NOT IN

      -- list the child tables which are not grand childs for the non grand parent/child tables
      FOR J IN (SELECT DISTINCT dc1.table_name, dc2.table_name child_table
                  FROM dba_constraints dc1, dba_constraints dc2
                 WHERE     dc1.owner = 'DEAL'
                       AND DC1.TABLE_NAME = v_tbl_nm
                       AND dc1.owner = dc2.owner
                       AND dc2.r_constraint_name = dc1.constraint_name
                       AND NOT EXISTS
                              (SELECT 1
                                 FROM grand_child_tbls
                                WHERE table_name = dc2.table_name))
         INSERT INTO new_parent_child_tbls (table_name, child_table)
              VALUES (v_tbl_nm, j.child_table);
      END LOOP;


      -- -- list the parent tables which are not grand grand parents for the non grand parent/child tables
      FOR k IN (SELECT dc1.table_name, dc2.table_name parent_table
                  FROM dba_constraints dc1, dba_constraints dc2
                 WHERE     dc1.owner = 'DEAL'
                       AND DC1.TABLE_NAME = v_tbl_nm
                       AND dc1.owner = dc2.owner
                       AND dc2.constraint_name = dc1.r_constraint_name
                       AND NOT EXISTS
                              (SELECT 1
                                 FROM grand_parent_tbls
                                WHERE table_name = dc2.table_name))
         UPDATE new_parent_child_tbls
            SET parent_table = k.parent_table
          WHERE table_name = v_tbl_nm;
      END LOOP;



Hmmm...I'm not completely sure what you are trying to accomplish with that and your custom table that I do not recognize.  However, in a past life I did some work using Oracles constraint table to derive the relationship "levels". So, with advance apologies for capitalization, missing semi-colons, unmatched BEGIN-END's, and other syntax errors, here is some untested code that reconstructed from memory (as I don't have an Oracle instance available, right now).

I'm quite sure it won't work :>).  But, perhaps you can tweak it to a running state and see if it produces something like what you are looking for.  In any case, the WHILE loop that is deriving the level may be useful.
   V_TOP_NM    VARCHAR2 (50);
   V_TOP_OWN   VARCHAR2 (50);
   V_TBL_NM    VARCHAR2 (50);
   V_TBL_OWN   VARCHAR2 (50);
   V_CHI_NM    VARCHAR2 (50);
   V_CHI_OWN   VARCHAR2 (50);
   V_PAR_NM    VARCHAR2 (50);
   V_PAR OWN   VARCHAR2 (50);
for T IN (
 c.owner as chi_own
,c.table_name as chi_nm
,p.owner as par_own 
,p.TABLE_NAME as par_nm 
,t.owner as tab_own  
,t.TABLE_NAME as tab_nm 
from dba_constraints as t 
left join dba_constraints as p
 and t.R_OWNER = p.owner
left join dba constraints c
  on t.constraint_name = c.r_constraint_name
 and t.Owner = c.r_owner 
t.constraint_type in ('R','P','U') 
--count hierarchy depth
   V_TBL_LVL := 0;
   V_TAB_OWN := T.tab_own;
   V_TAB_NM := t.Tab_nm;
   WHILE v_tab_nm IS NOT NULL
     select p.owner, p.TABLE_NAME 
       into V_Tab_OWN, V_Tab_NM
       from dba_constraints as c inner join dba_constraints as p
         on c.r_owner = p.owner and c.r_constraint_name = p.constraint_name
      where c.constraint_type = 'R';
        and c.owner = v_tbl_own
        and c.table_name = v_tbl_nm;
     V_TBL_LVL := V_TBL_LVL - 1;
     when NO_DATA_FOUND
          V_Tab_NM := NULL;

--these contain table name, immediate parent, immediate child, top level and depth from top   
   V_TBL_NM    := t.tab_nm;
   V_TBL_OWN   := t.tab_own;
   V_TOP_NM    := v_tab_nm;
   V_TOP_OWN   := v_tab_own;
   V_CHI_NM    := t.chi_nm;
   V_CHI_OWN   := t.chi_own;
   V_PAR_NM    := t.Par_Nm;
   V_PAR OWN   := t.par_own;
--do something here


Open in new window

sarran_vAuthor Commented:
After trying many ways, I have spent two working days to wrap the hierarchy of this complicated OLTP system manually by using the list of parent child tables i have found from the code i tried with hell of temporary tables. Thanks much for your support. It's great that you guyz jump in as soon as you can into the problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now