Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Can we ALTER INDEX to have UNIQUE?

SOLVED
Go to solution

Can we ALTER INDEX to have UNIQUE?

ORACLE 10G 2

ALTER INDEX IS_ALONE UNIQUE.

I want alter a index to have the UNIQUE instead of NONUNIQUE, is there a way without create or just drop/create? This might be a dumb question but I have to know.

6 REPLIES
Oliver Wriedt
Valued Contributor
Solution

Re: Can we ALTER INDEX to have UNIQUE?

As far as i know, you can not change or rebuild the index to unique.
But it is possible to add a unique constraint to the table:

alter table dummy add constraint pk unique (id);

Regards
Oliver

Re: Can we ALTER INDEX to have UNIQUE?

then would it be a good idea to drop the index and then create?

Re: Can we ALTER INDEX to have UNIQUE?

This reason is I am going to do this using cursor and dynamically rename indexes and with a few I am making sure there are unique as they should be.
Eric Antunes
Honored Contributor

Re: Can we ALTER INDEX to have UNIQUE?

Hi Stephen,

The answer is no, you cannot alter an index from non-unique to unique at least until 9i. Since you are on 10G R2 you can always try it. ;) But the most important consideration is that you must have unique values in the unique index column(s) to be able to create it!

Best Regards,

Eric Antunes
Each and every day is a good day to learn.

Re: Can we ALTER INDEX to have UNIQUE?

I was keeping in mind that the data will have to be unique. Thank you for answering the question and I will try to see if I can "ALTER INDEX"
Volker Borowski
Honored Contributor

Re: Can we ALTER INDEX to have UNIQUE?

Hi,

adding a PK constraint will create an internal index SYS.... that will do the job, so from terms of space used, you will have the same result as if a second index is created.

If application is up and runnning while you do this, the index should stay in place for performance reasons.

Go by

alter index is_alone rename to is_alone_to_be_dropped;

create unique index is_alone on ..... parallel 8;
-- check result, in case duplicate keys are present
alter index is_alone noparallel;
drop index is_alone_to_be_dropped;

You'll need the space for the second index at least temporaryly.
Volker