Re: anyway to alter a constraint to make it deferrable?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 2 Apr 2004 06:20:51 +1000
Message-ID: <406c79a8$0$441$afc38c87@news.optusnet.com.au>

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:406C6ED0.47F05B30_at_remove_spam.peasland.com...
> Ryan Gaffuri wrote:
> >
> > I don't want to drop and re-create it, since there are cascading
> > foreign keys. I don't see it in the docs.
> >
> > alter table x modify (constraint x_fk deferrable);
> >
> > I cant find the proper syntax?
>
> Straight from the Oracle docs:
>
> "You cannot change the state of a NOT DEFERRABLE constraint to INITIALLY
> DEFERRED."
>
> Otherwise, remove the parentheses in your syntax....
>
>
> HTH,
> Brian
>

Hmmm.

SQL> alter table emp add (constraint nameuq unique (ename) deferrable initially deferred);

Table altered.

SQL> alter table emp modify constraint nameuq disable;

Table altered.

SQL> alter table emp modify constraint nameuq enable;

Table altered.

SQL> alter table emp modify constraint nameuq validate;

Table altered.

SQL> alter table emp modify constraint nameuq novalidate;

Table altered.

SQL> alter table emp modify constraint nameuq initially immediate;

Table altered.

SQL> alter table emp modify constraint nameuq initially deferred;

Table altered.

SQL> alter table emp modify constraint nameuq not deferrable; alter table emp modify constraint nameuq not deferrable

                                         *

ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter table emp modify constraint nameuq deferrable; alter table emp modify constraint nameuq deferrable

                                         *

ERROR at line 1:
ORA-00933: SQL command not properly ended

The syntax diagrams for 9i at tahiti don't appear to be telling the entire story.
Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
===========================================

Received on Thu Apr 01 2004 - 14:20:51 CST