'Making unique index case-insensitive

I have a unique index on the table and I need to make it case-insensitive, because I started getting duplicates in that table:

TEST
Test
TeSt

To fix the issue I was trying to drop existing index and re-create it:

 ALTER TABLE table1 drop constraint test_uk1;
 DROP INDEX test_uk1;
 CREATE UNIQUE INDEX test_uk1 ON table1(UPPER(column1));

 ALTER TABLE table1 ADD CONSTRAINT test_uk1 UNIQUE (column1) USING INDEX test_uk1 ENABLE VALIDATE;

I got an error ORA-14196: Specified index cannot be used to enforce the constraint. on the last statement.

Is there a better way to accomplish this with Oracle?

sqloracle


Solution 1:[1]

You can use a virtual column to normalise the case of the string and then put the unique constraint on the virtual column:

CREATE TABLE table_name (
  value  VARCHAR2(20),
  uvalue VARCHAR2(20)
         GENERATED ALWAYS AS (UPPER(value))
         CONSTRAINT table_name__uvalue__u UNIQUE
);

Then:

INSERT INTO table_name (value) VALUES ('abc');
INSERT INTO table_name (value) VALUES ('aBc');

Inserts one row and the second statement fails with:

ORA-00001: unique constraint (FIDDLE_QDGWKZFBAWPLSRVHJNHN.TABLE_NAME__UVALUE__U) violated

db<>fiddle here

Solution 2:[2]

You need not to ALTER TABLE table1 ADD CONSTRAINT ..., the definition of the functional based index is enough for the creation of the unique constraint.

It is also wrong, as you use UNIQUE (column1) which you do not want, so simple skip this statement.

CREATE UNIQUE INDEX test_uk1 ON table1(UPPER(column1));

insert into table1 (column1) values('x');
insert into table1 (column1) values('X');

ORA-00001: unique constraint (XXX.TEST_UK1) violated

Solution 3:[3]

Even independently of the DBMS (Oracle, SQL Server, DB2, Vertica, PostgreSQL, you name them), Indexes live and die with the fact that data is not modified/derived before the index or the optimisation taking advantage of the index is applied.

So your approach is doomed, as far as I can tell.

Try this:

ALTER TABLE table1 ADD column1_u DEFAULT UPPER(column1);
CREATE UNIQUE INDEX test_uk1 ON table1(column1_u);
ALTER TABLE table1 ADD CONSTRAINT test_uk1 UNIQUE(column1_u) 
 USING INDEX test_uk1 ENABLE VALIDATE;

I know, it's a long transaction if your table is big, as you need to shuffle half the tablespace, but it's worth it ..

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MT0
Solution 2 Marmite Bomber
Solution 3 marcothesane

Related Questions