This content has been marked as final. Show 6 replies
984180 wrote:How do I ask a question on the forums?
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..
SQL and PL/SQL FAQ
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
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;