About foreign key constraints(find parent and child tables for specific table).

below is the sql script to find the foreign key relation to the reference parent table...

use below script to findout parent and child tables...


_________________________________________________________________


accept tablename prompt 'Enter the table name:'
accept tableowner prompt 'Enter the table owner name:'

SET LINESIZE 255
set verify off
col GIVEN_TABLE for a30
col PARENT_TABLE for a30
col CHILD_TABLE for a30
col CHILD_TABLE_OWNER for a20
col GIVEN_TABLE_OWNER for a20
col PARENT_TABLE_OWNER for a20
col PARENT_TAB_CONST for a30
col CHILD_TAB_FK_CONST for a30
col G_TAB_columns for a30
col GIVEN_FK_CONST_NAME for a30
col G_TAB_FK_COLUMNS for a30
col GIVEN_TAB_CONST for a30

Prompt Below are the Child tables for &tablename table.
prompt -----------------------------------------------


SELECT p.table_name GIVEN_TABLE, p.owner GIVEN_TABLE_OWNER, p.constraint_name GIVEN_TAB_CONST,
LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_columns
,c.owner CHILD_TABLE_OWNER,c.table_name CHILD_TABLE,c.constraint_name CHILD_TAB_FK_CONST
FROM dba_constraints p, dba_constraints c,dba_cons_columns t
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
and p.table_name=t.table_name
and p.constraint_name=t.constraint_name
AND p.table_name = UPPER('&tablename')
and p.owner=('&tableowner')
group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

Prompt Below are the Parent tables for &tablename table.
prompt ------------------------------------------------


SELECT c.table_name GIVEN_TABLE,c.owner GIVEN_TABLE_OWNER, c.constraint_name GIVEN_FK_CONST_NAME,
LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_FK_columns,p.owner PARENT_TABLE_OWNER,p.table_name PARENT_TABLE
,p.constraint_name PARENT_TAB_CONST
FROM dba_constraints p, dba_constraints c ,dba_cons_columns t
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
and c.table_name=t.table_name
and c.constraint_name=t.constraint_name
AND c.table_name = UPPER('&tablename')
and c.owner='&tableowner'
group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;


_________________________________________________________________________



Foreign key constraints(on child table) can only be enabled when the primary constraint(unique) is enabled on the parent table so that the data in the parent table can be protected, which means if you want to delete some rows or add any rows(if it is duplicate row). if we want to enable the disabled foreign key then there should not be any orpan rows in the child table... so rows had to be deleted to make it work...

Hope this helps....