Parent-Child

[st2000]

Hi:
I want to know the child tables first and the parent tables. Is there a query where i can get something like this. If there is a procedure that is also good but I want to be able to enter the schema or owner name and then should be able to list the tables as per children first and then parents..
Thanks

[jgmagnus]

Use a tool like TOAD.
Those are very helpful

- Magnus

[pando]

You just want to know the child table names?

Normally if I dont use GUI tool I find out the PK of parent tables then using dba_constraints I find the child tables

select table_name
from dba_constraints where r_constraint_name='PARENT_TABLE_PK'

dunno if this helps you

[dragon99]

set echo off
set verify off
col TABLE_NAME format a30
col COLUMN_NAME format a20
col POSITION format a30
Try the following script. It will ask you for your tablename.when you supply tablename, it will show the parent table of the tablename or the child table of the tablename you supplied.

dragon


accept xTable prompt 'Enter Table Name: '
TTITLE LEFT 'Child Tables for the table: '&xTABLE
break on TABLE_NAME
SELECT B.TABLE_NAME, C.COLUMN_NAME, C.POSITION
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND A.TABLE_NAME = C.TABLE_NAME
AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND A.TABLE_NAME = UPPER('&xTable')
ORDER BY B.TABLE_NAME, C.POSITION;
TTITLE LEFT 'Parent tables for the table: '&xTable
SELECT A.TABLE_NAME, C.COLUMN_NAME, C.POSITION
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND B.TABLE_NAME = C.TABLE_NAME
AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND B.TABLE_NAME = UPPER('&xTable')
ORDER BY A.TABLE_NAME, C.POSITION;

[st2000]

Not Just one tablename and its parent or child..
I want to be able to do it for an entire schema.. And this is not to simply see (using TOAD or whatever) but further insert into schema with constraints, so to avoid any problems..
Thanks

[sree_sri]

select b.table_name Child_table,a.table_name parent_table ,b.constraint_name,b.r_constraint_name
from user_constraints b,user_constraints a
where a.constraint_type='P' And
a.constraint_name=b.r_constraint_name
order by b.table_name;

Try this query. It will give the child table_name,parent table_name,constraint_name,r_constraint_name. It will give order by Child table.

[jgmagnus]

Well, if you are familiar with your data you are inserting then just DISABLE the FK constraints, INSERT the data, then ENABLE the constraints again.

If one of the constraints cannot enable, then just run a diagnostic on the two tables.

Is this just not feasable?

- Magnus

[st2000]

Yap.. Sree_sri.. It works.. Great .. Thanks