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

In article <423b5ab1.0210161551.236169d7_at_posting.google.com>, Sharkie <sharkdba_at_yahoo.com> writes:
>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".

I think after checking the 8.1 SQL manual constrain clause syntax that your only option is to drop and re-create the FK. To save time you could perform the re-create using the novalidate option.

HTH -- Mark D Powell --

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net