Dependend Constratins

[irehman]

I have a question How can I find out which tables are referencing the Primary key.

Example:
I have a Table Called EMP and this table has PK on EMPNO, Now how can I find out which other tables are referencing this Primary Key.

Thanks in Advance.

[dknight]

I do not have a script available, but if you use

DBA_CONSTRAINTS and
DBA_CONS_COLUMNS

you can find what you are looking for.

Hope that helps.

[nealh]

Something along the lines of:

select uc2.table_name parent_table,
uc.table_name child_table,
ucc.column_name child_column, ucc2.column_name parent_column
from all_constraints uc,
all_cons_columns ucc,
all_constraints uc2,
all_cons_columns ucc2
where uc.r_constraint_name = uc2.constraint_Name
and ucc2.table_name = uc2.table_name
and ucc2.owner = uc2.owner
and ucc2.constraint_Name = uc2.constraint_Name
and uc2.constraint_type = 'P'
and uc.constraint_type = 'R'
and uc.owner = ucc.owner
and uc.constraint_Name = ucc.constraint_Name
and uc2.table_name = 'XXXX'
and uc2.owner = 'XXXX'
order by 1,2,3

[ocp2b]

I use this script to find the relationships.

column acn heading "FK-CONS" for a20
column act heading "T" for a1
column atn heading "FK-TBL" for a15
column arcn heading "R-CONST" for a15
column bcn heading "PK-CONS" for a15
column bct heading "PT" for a2
column btn heading "PK-TBL" for a15

select a.constraint_name "acn",
a.constraint_type "act",
a.table_name "atn",
a.r_constraint_name "arcn",
b.constraint_name "bcn",
b.constraint_type "bct",
b.table_name "btn"
from dba_constraints a, dba_constraints b
where a.owner='xyz'
and b.owner='xyz'
and a.r_constraint_name = b.constraint_name
order by b.table_name
/


Basically, this query will select the foreign-key constraint, the table it is defined on, the type of constraint it is (in this case a referential constraint), the constraint it references, and the table the constraint is on.

example.
Dept table has a PK on Deptno called pk-key
Emp table has a constraint on deptno called dept-fkey.

The query would return:
dept-fkey, R, Emp,pk-key,pk-key,P,Dept

dept-fkey is a referential constraint on emp table referencing pk-key which is a primary key on table dept.

Hope this helps.