Home » SQL & PL/SQL » SQL & PL/SQL » Deferred Constraint Without Dropping Existing Constraint (10g Rel 2)
Deferred Constraint Without Dropping Existing Constraint [message #334724] Thu, 17 July 2008 15:08 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I have an exisitng Unique Constraint on 2 Coulmn (colA,colB ).
For some business Reasons we want to modify that constraint to be Deferrable initiallay deffered.

Is there a way without dropping exisitng Constraint and Index i can modify that constraint to be deferrable.

I know i can drop and then modify the table and add a new constraint and Index, but that will take time ( for 700 mil rows table).

alter table t add constraint Const_name unique (colA,ColB)deferrable initially deferred;

This has Unique index also, which is important .

Is there any Syntax or better Approach please share.

Thanks


[Updated on: Thu, 17 July 2008 15:09]

Report message to a moderator

Re: Deferred Constraint Without Dropping Existing Constraint [message #334730 is a reply to message #334724] Thu, 17 July 2008 15:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
took me 2 minutes to find this, so you should have found this as well. What am I reading wrongly in either the docs or in your question?
Re: Deferred Constraint Without Dropping Existing Constraint [message #334735 is a reply to message #334730] Thu, 17 July 2008 15:39 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Frank for pointing me that Doc,

It's clear that we can't modify a constraint to be deferrable,
So the only option is to Drop and recreate it.


If i drop and recreate with this syntax , It creates a Deferrable Constraint but a NON Unique Index also is created, but we want a Unique one.
alter table t add constraint Const_name unique (colA,ColB)deferrable initially deferred;


If i used this syntax it fails
ALTER TABLE t ADD ( CONSTRAINT Const_name  
UNIQUE (A,B) deferrable initially deferred) using index
(create unique index Const_name   on t (A,B) tablespace Index_X);

ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


Is this possible , What's wrong in my alter statement.

Thanks Again
Re: Deferred Constraint Without Dropping Existing Constraint [message #334750 is a reply to message #334735] Thu, 17 July 2008 16:25 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Though I have never had the need to test it, it would seem that the combination of the index and the unique constraint would combine to give you everything a unique index would. What is so important about the index actually having the unique clause on it?
Re: Deferred Constraint Without Dropping Existing Constraint [message #334756 is a reply to message #334724] Thu, 17 July 2008 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
similar to the example below:
15:02:27 SQL> drop table id10t;

Table dropped.

15:02:29 SQL> create table id10t (val number);

Table created.

15:02:29 SQL> create index val_ndx on id10t (val);

Index created.

15:02:29 SQL> insert into id10t values(1);

1 row created.

15:02:29 SQL> insert into id10t values(2);

1 row created.

15:02:29 SQL> commit;

Commit complete.

15:02:29 SQL> alter table id10t add primary key (val);

Table altered.

15:02:29 SQL> insert into id10t values(2);
insert into id10t values(2)
*
ERROR at line 1:
ORA-00001: unique constraint (DBADMIN.SYS_C00163742) violated


15:02:29 SQL> 



using a non-unique index!
Re: Deferred Constraint Without Dropping Existing Constraint [message #334764 is a reply to message #334756] Thu, 17 July 2008 21:50 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A deferrable UNIQUE constraint must be supported by a non-unique index. Your index MUST be non-unique.

Oracle will still enforce uniqueness, but only at commit time. It will do so NOT using a unique index, but with (I believe) recursive SQL instead.

Ross Leishman
Previous Topic: Procedure not compiled successfully. But shows no error
Next Topic: query on emp table
Goto Forum:
  


Current Time: Sat Dec 03 05:51:39 CST 2016

Total time taken to generate the page: 0.05095 seconds