Re: primary and foreign keys

From: <mark.powell_at_eds.com>
Date: Tue, 02 Mar 1999 13:43:39 GMT
Message-ID: <7bgpu8$nts$1@nnrp1.dejanews.com>

In article <19990301192803.20412.00001773_at_ng-fz1.aol.com>,   vtuttl_at_aol.com (Vtuttl) wrote:
> Is there any way to get Oracle to specify the primary and foreign keys while
> querying a table? Also is there a way to get the foreign key table reference?
> thanks!
> hidden_at_users.request
>

The dictionary view sys.dba_constraints contains the information you are looking for. Here is a script to get you started.

set echo off
rem
rem SQL*Plus script to locate foreign keys for all tables rem
rem 1995 02 05 m d powell New script rem 1998 06 23 m d powell New version using join rem
rem add 'table_name like' or '=' to limit output rem
set verify off

column FKN               format a30  heading "Foreign Key Name"
column FKT               format a30  heading "Referencing Table"
column RT                format a30  heading "Referenced Table"
column RCN               format a30  heading "Referenced Constraint"
column Status            fold_after

select
       a.constraint_name    "FKN"     ,
       a.table_name            "FKT"     ,
       a.delete_rule             "Rule"    ,
       a.status                     "Status"  ,
       b.table_name            "RT"      ,
       a.r_constraint_name "RCN"

from sys.dba_constraints a , sys.dba_constraints b where a.r_constraint_name = b.constraint_name order by a.constraint_name
/
undefine tbl_nm

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 02 1999 - 07:43:39 CST