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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Roman.

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

Answered by: Chris Saxon - Last updated: April 09, 2020 - 10:26 am UTC

Category: Database Development - Version: 19c

Viewed 1000+ times

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 we 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/

More to Explore

Design

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