Go Premium for a chance to win a PS4. Enter to Win


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
  • 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

824 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