From: "Van Messner" <vmessner@bestweb.net>
Newsgroups: comp.databases.oracle.server
References: <8rvob0$i50$1@nnrp1.deja.com> <971214678.2789.3.pluto.d4ee154e@news.demon.nl>
Subject: Re: Foreign keys.
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <9hNE5.5747$mC.389522@monger.newsread.com>
Date: Tue, 10 Oct 2000 23:05:41 GMT
NNTP-Posting-Host: 216.179.16.67
X-Complaints-To: Abuse Role <abuse@bestweb.net>, We Care <abuse@newsread.com>
X-Trace: monger.newsread.com 971219141 216.179.16.67 (Tue, 10 Oct 2000 19:05:41 EDT)
NNTP-Posting-Date: Tue, 10 Oct 2000 19:05:41 EDT
Organization: BestWeb (bestweb.net)


ACCEPT tname PROMPT 'Enter Name of Table You Want to Look at:'
ACCEPT qowner PROMPT 'Enter Name of Table Owner:'
column fowner heading 'Owner Of |Table With|Foreign Key' format a11
column ftable heading ' Name Of | Table With| Foreign Key' format a16
column fconname heading '      FK Name ' format a22
column fdel heading 'Delete|Rule' format a10
column fref heading 'Constraint in|Parent Table|Pointed to by FK' format a22
column fstatus heading 'FK Status' format a8
TTITLE ' FOREIGN KEYS THAT POINT TO THIS TABLE '
SELECT
  frn.owner fowner,
  frn.table_name ftable,
  frn.constraint_name fconname,
  frn.delete_rule fdel,
  frn.r_constraint_name fref,
  frn.status fstatus
FROM
  dba_constraints par,
  dba_constraints frn
WHERE
  par.table_name = UPPER('&tname')
  and par.owner = UPPER('&qowner')
  and par.constraint_name = frn.r_constraint_name
;


"Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
news:971214678.2789.3.pluto.d4ee154e@news.demon.nl...
> select r.table_name, r.constraint_name
> from user_constraints c, user_constraints r
> where c.table_name = '<your table>'
> and   c.constraint_type='P'
> and   r.referenced_name=c.constraint_name
> and   r.referenced_owner=c.owner
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
>
> <vdolt@my-deja.com> wrote in message news:8rvob0$i50$1@nnrp1.deja.com...
> > I am trying to delete a record from the table. Other tables have
> > foreign keys on that table, which prevents me from deleting original
> > record. Is there a query I can run to uncover all the foreign key on
> > that table?
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>