Re: Selecting list of foreign keys enabled on a table

From: Mark Powell <Mark.Powell_at_eds.com>
Date: 22 Jul 1998 13:24:29 GMT
Message-ID: <01bdb573$eb656840$a12c6394@J00679271.ddc.eds.com>

Alex, here is a script that will find the foreign key, FK, references

set echo off
rem
rem 1996 07 16 m d powell New script rem 1998 06 23 m d powell Modify for general use; add more columns rem
rem remove 'table_name like' line and all constraints show up rem
column constraint_name format a30
column r_constraint_name format a30
column table_name format a30
accept tbl_nm prompt "Enter table name to show referential constraints on => "

select

       b.table_name        "Referenced Table"     ,
       a.table_name        "Referencing Table"    ,
       a.delete_rule       "Rule"                 ,
       a.status            "Status"               ,
       a.r_constraint_name "Referenced Constraint",
       a.constraint_name   "Foreign Key Name"

from sys.dba_constraints a , sys.dba_constraints b where b.table_name like upper('&tbl_nm'||'%') and a.r_constraint_name = b.constraint_name /
undefine tbl_nm

Alexander Bisset <a-bisset_at_aberdeen-harbour.co.uk> wrote in article >>...
> I wish to truncate a table but I am getting a "ORA-02266: unique/primary
> keys in table referenced by enabled foreign keys" message.
>
> I understand why this is happening what I would like to know is how I can
> get a list of all foreign keys that are referencing the table I want to
> drop. I thought I had disabled all of them but there must be one eluding
me.
>
> Thanks in advance,
Received on Wed Jul 22 1998 - 08:24:29 CDT