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