Newsgroups: comp.databases.oracle
Subject: Re: Using alter table add constraint syntax ?
References: 

esha1@cix.compulink.co.uk ("Mike Ziemann East Su") writes:

>This is a line from my create table statement which successfully creates 
>a constraint on a table T95  :

>SPARE_1                 NUMBER(10)   CONSTRAINT IDX_T95 UNIQUE USING
>INDEX 
>STORAGE(INITIAL 3000K NEXT 250K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0)
>TABLESPACE CMDS95,
>my problem is that I want to delete this constraint then recreate it in
>a different tablespace.  I deleted it with:

>alter table T95 drop constraint idx_t95 cascade;
> but I just cant work out the alter table add syntax to create it
> somewhere else!! Can anybody help ?
> Please email answer to
>
> esha1@cix.compulink.co.uk
> Thanks
> Mike

Mike,

Issue the following statement:

alter table T95 add (constraint idx_t95 unique (COLUMN_LIST)
                     using index tablespace NEW_TABLESPACE
                           storage (initial 3000k next 250k
                                    minextents 1  maxextents 99
                                    pctincrease 0));

The COLUMN_LIST is the columns in order that the index should be based on.
Replace NEW_TABLESPACE with the tablespace that the index should be
created in.
Also, the "using index" clause enforces the uniqueness with an index that has
the same name as the constraint. This clause should only be used for
unique and primary key constraints. In your case it is.


-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: www.arikaplan.com                          <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page ndex.htm">Back to Ari Kaplan's Home Page