Can we ALTER INDEX to have UNIQUE?

[Stephen Ray Badgett]

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.

[Oliver Wriedt · Accepted solution]

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

[Stephen Ray Badgett]

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

[Stephen Ray Badgett]

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]

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

[Stephen Ray Badgett]

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]

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