Skip to Main Content
  • Questions
  • Converting Unique Index to Non-Unique

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Roman.

Asked: April 09, 2020 - 9:53 am UTC

Last updated: November 11, 2022 - 3:29 am UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

Hi, TOM!

Some time ago I decided to change my unique index to non-unique because of new requirements. I googled a lot but didn't find anything about convertation.
Though I found solutions like creating new nnon-unique index with extra constant field in it and then deleting unique index or just drop-create solution.
But I wanted to know why exactly there is no option to alter unique index to non-unique? Is there some internal storage stuff that is different between unique and non-unique indexes?

and Chris said...

There's no way to alter a non-unique index to be unique.

Remember that a unique index is a form of constraint. You're saying that there can only be one row for each set of values in the column list.

In the general case, this requires you to use clean the data before you can apply the constraint.

From 12c you can create many indexes on the same column list. Provided:

- The have different properties (uniqueness, partitioning, bitmap vs. btree, ...)
- Only one is visible

So you can swap a non-unique and unique index over doing something like this:

create table t (
  c1 int
);

create index i 
  on t ( c1 );
  
create unique index ui 
  on t ( c1 )
  invisible;
  
alter index i
  invisible;

alter index ui 
  visible;


Note this means there's a brief time when there's no visible indexes on c1! You may want to have a short outage while swapping the indexes over.

There are also a whole bunch of subtle differences between unique and non-unique indexes. Richard Foote discusses these in a series starting at: https://richardfoote.wordpress.com/2007/12/18/differences-between-unique-and-non-unique-indexes-part-i/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Answering the wrong question

Rob, November 10, 2022 - 6:42 pm UTC

The question was how to make an already unique index non-unique. You answered as if the question was the other way around. I see no reason why the unique constraint can't be removed from an index.
Connor McDonald
November 11, 2022 - 3:29 am UTC

You could drop a unique constraint that is underpinned by a non-unique index.

However if you dropped a unique constraint that was underpinned by a unique index (which would be default when you created the constraint) then that index is still unique.

Unique indexes are structured differently to a non-unique one, so you would need to rebuild/recreate it no matter which direction you wish to convert.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.