Re: Foreign keys.

From: Van Messner <vmessner_at_bestweb.net>
Date: Tue, 10 Oct 2000 23:05:41 GMT
Message-ID: <9hNE5.5747$mC.389522@monger.newsread.com>

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_at_sybrandb.demon.nl> wrote in message news:971214678.2789.3.pluto.d4ee154e_at_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_at_my-deja.com> wrote in message news:8rvob0$i50$1_at_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.
>
>
Received on Tue Oct 10 2000 - 18:05:41 CDT