Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2011-09-02
Medium Priority
Last Modified: 2012-08-28
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.
Question by:sarran_v
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 74

Expert Comment

ID: 36477827
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?
LVL 42

Expert Comment

ID: 36478181
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.


Author Comment

ID: 36479135
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;



LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 36485007
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


Author Comment

ID: 36557875
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.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This video shows how to recover a database from a user managed backup
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question