Re: how do I remove "on delete cascade"?

From: MarkP28665 <markp28665_at_aol.com>
Date: 30 Dec 1998 22:43:39 GMT
Message-ID: <19981230174339.22192.00004200@ng134.aol.com>

Brian Lavender >>
I created a table with a "on delete cascade" referencing a foreign key. I decided I want to remove that parameter. How do I do that? <<

Find the foreign key name in sys.dba_constraints and drop it using an 'alter table table_name drop constraint constraint_name;'

Here is a useful script for finding FK's on a table. Warning, you may need to add the owner column as I do not allow the same table name to appear under multiple owners on my production system.

rem
rem SQL*Plus script to locate foreign keys for a specific table 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

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Wed Dec 30 1998 - 16:43:39 CST