6 Replies Latest reply: Jan 25, 2013 2:09 PM by onedbguru RSS

    Parent Child Relationship

    987183
      Hi,

      My Schema having lots of tables. I dont know which one is parent table and which one is child tables.

      So here i want know the details about that .

      And i need a procedure if i pass the parent table as a parameter all the referenced tables and parent table wants deletes..
        • 1. Re: Parent Child Relationship
          sb92075
          984180 wrote:
          Hi,

          My Schema having lots of tables. I dont know which one is parent table and which one is child tables.

          So here i want know the details about that .

          And i need a procedure if i pass the parent table as a parameter all the referenced tables and parent table wants deletes..
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          query ALL_CONSTRAINTS
          • 2. Re: Parent Child Relationship
            stefan nebesnak
            Do you mean Delete? Drop? Truncate?
            • 3. Re: Parent Child Relationship
              987183
              Delete..
              • 4. Re: Parent Child Relationship
                onedbguru
                Unless the Dev DBA that designed this schema actually documented the P/C relationship, good luck on getting it correct - especially if there are no actual FK's defined. and having column names the same are not necessarily an indication they are related. If all of the tables do have actual FK's then you may be able to use OEM (db or grid control) to see "dependents" and "dependencies". These buttons will interogate the DBA_DEPENDENTS table.

                You will need to ask the previous DBA and/or the development staff to provide the correct logical design. This problem indicates the inability of a lot of IT "professionals" to write proper documention. ("We don't need no stinkin' documentation we are using "AGILE" or "RAD" development processes."

                from a quick google search on the subject of finding P/C relationships :
                sql query to find dependencies for a table
                • 5. Re: Parent Child Relationship
                  stefan nebesnak
                  statement below can be helpful:
                  select t.table_name, level 
                  from user_tables t,
                       user_constraints c1,
                       user_constraints c2
                  where t.table_name = c1.table_name
                    and t.table_name = c2.table_name(+)
                    and c1.constraint_type in ('U', 'P') --"U - Unique key, P - Primary key"
                    and c2.constraint_type(+)='R' --"R - Referential integrity"
                  start with t.table_name = upper('&v_table_name')
                  connect by prior c1.constraint_name = c2.r_constraint_name
                  order by level desc;
                  • 6. Re: Parent Child Relationship
                    onedbguru
                    stefan nebesnak wrote:
                    statement below can be helpful:
                    Yes, but as stated, only if they actually used constraints/FKs/Triggers etc... I have seen way too many apps where the logical design was in the app code and not in the db.