Unique with Non-unique

A not very well advertised new feature of Oracle 8i (and even less well documented) is the ability now to enforce a unique constraint using a non-unique index. This can reduce the number of indexes you need to have on a table.

Basic timing tests indicate that the uniqueness checking is no slower than using a unique index.

There is no explicit syntax to use this new feature, so the trick is to:

  1. Create the unique constraint DISABLED
  2. Create your index
  3. Enable the constraint 

CREATE TABLE test_unique (
  num_col1 NUMBER(10),
  num_col2 NUMBER(10),
  char_col CHAR(50))
tablespace users;

ALTER TABLE test_unique ADD constraint test_unique_pk
primary key ( num_col1) disable;

CREATE INDEX test_unique_ix ON test_unique (num_col1, num_col2);

ALTER TABLE test_unique enable constraint test_unique_pk;