Re: how to remove "on delete cascade" constraint clause?

sharkdba_at_yahoo.com (Sharkie) wrote in message news:<423b5ab1.0210161551.236169d7_at_posting.google.com>...
> This seems like a simple task, but anywhere I look,
> I see syntax on how to add "on delete cascade" to
> a foreign key constraint. However, how do I remove it
> without dropping and recreating the constraint?
>
> I tried:
>
> alter table <table2_name> modify (<col2_name> constraint
> <constraint_name> foreign key references <table1_name>
> (<column1_name>))
>
> [without the "on cascade delete"]
>
> but get this error:
> ORA-02253: constraint specification not allowed here
>
> An example:
> create table temp1 (temp1intid int primary key, tempname1 varchar2(100));
> create table temp2 (temp2intid int, tempname2 varchar2(100));
> alter table temp2 add constraint fk_temp2_01 foreign key
> (temp2intid) references temp1 (temp1intid) on delete cascade;
>
> now, I'd like to keep the fk_temp2_01 foreign key, but remove the
> "on delete cascade".

The only solution is drop and re-create

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Oct 17 2002 - 02:35:53 CDT