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

Posted on 2011-09-02
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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 39
How to fix Datetime in MySQL? 4 64
Convert VBA UDF to SQl SERVER UDF 4 46
Select the 2 most recent visit dates 5 9
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

685 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