OracleBrains.Com header image 2

creating UNIQUE index on a table with existing non unique values.

May 7th, 2007 by Rajender Singh · 2 Comments

Today I came up with this challenge of creating UNIQUE index on a table with exiting non unique value. After lot of r&d I came up with following very interested solution:

I am using scott schema to show my solution.

SQL> create table test as select deptno from emp;

Table created.

SQL> select deptno from test;

DEPTNO
———-
20
30
30
20
30
30
10
20
10
30
20

DEPTNO
———-
30
20
10

14 rows selected.

SQL> alter table test add constraint unique_index unique(deptno) disable;

Table altered.

SQL> create index unique_index on test(deptno);

Index created.

SQL> alter table test enable novalidate constraint unique_index;

Table altered.

SQL> insert into test values (20);
insert into test values (20)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UNIQUE_INDEX) violated


Tags: Oracle Administration · Oracle Database

2 responses so far ↓

  • 1 S. Inderjeet Singh // Jun 7, 2007 at 8:13 pm

    Hi,

    Are you sure this is unique index which is causing the error. Its not uniqiue index, rather its unique constraint. Index created is still non-unique in nature.

    17:45:55 SQL> SELECT index_name, index_type, table_name, uniqueness
    17:46:23 2 FROM user_indexes
    17:46:29 3 WHERE index_name = ‘UNIQUE_INDEX’;

    INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES
    —————————— ————————— —————————— ———
    UNIQUE_INDEX NORMAL TEST123 NONUNIQUE

    Is it correct, or am I interpretting it wrongly.

    Regards
    S. Inderjeet Singh

  • 2 Rajender Singh // Jun 7, 2007 at 9:56 pm

    Hi,

    You are right Inder, Actually this blog entry should be named as enabling UNIQUE constraint on a table with existing non unique values.

    Regards,

    Raj

Leave a Comment