Re: Question about foreign keys
duffy_at_cais.cais.com (MCC) wrote:
>Hi All,
> I have a foreign key and need to find the table/column
>it is referencing, I've tried DBA_CONS_COLUMNS and DBA_CONSTRAINTS
>and could not get the clue, please help.
>Thanks,
>MCC
I use the following code to create a spool file to examine.
It lists Tables with foreign keys and which column in the
Referenced table's primary key the FK is pointing to.
It works OK for single columns keys, haven't tried it
yet on multiples.
set termout off
spool on
set linesize 132
column constraint_name format a20 heading "Foreign Key"
column table_name format a30 heading "Table"
column constraint_type heading "T"
column stat format a1 heading "Status"
column r_constraint_name format a15 heading "Primary Key"
break on table
break on r_constraint_name
break on b.table_name
select a.table_name, b.r_constraint_name,
b.table_name, b.constraint_name, b.constraint_type,
substr(b.status,1,1) stat
from user_constraints a, user_constraints b
where b.r_constraint_name = a.constraint_name
and b.constraint_type ='R'
order by a.table_name, r_constraint_name, b.table_name;
spool off
# now print the spooled file (e.g. on.lis,on.lst)
and you will have a map of the FK & PK relations.
Hope this helps.
Craig M. Wall
Received on Tue May 28 1996 - 00:00:00 CEST
Original text of this message
