Alter a nonunique index to a unique index

Question

I have a few non-unique constraints that I want to alter into unique constraints ( business rules have changed since the data model was made ). Is there any way to do it with out dropping and recreating as a unique constraint? I was thinking there would be an option in the alter constraint command, but I have not found anything.

Thanks!!

Answer 1

You cannot convert a non-unique index into a unique index.

(It's difficult to say what cannot be done. I'm basing this answer on looking at the ALTER INDEX page of the SQL Language Reference, searching for the word UNIQUE, and not finding any relevant hints. I looked at 11g instead of 10g, but that's probably better in this case because there are a few features that exist in 10g but are only documented in 11g.)

However, you can use a non-unique index for a unique constraint. But there are some performance considerations: a unique index would be smaller and faster.

create table my_table(a number);

create index my_table_index on my_table(a);

alter table my_table add constraint my_table_unique unique (a)
    using index my_table_index;

Answer 2

You can't modify a constraint in the way you wish you can only drop and recreate it. If you want to do this with no downtime then look into the DBMS_REDEFINITION package.