How to Create Case-Insensitive Unique Index

Case Insensitive Index

Using unique case-insensitive constraint in a column sometimes is reasonable in business logic to prevent duplicate items.

Let's see a table that should use case-insensitive unique constraint and why.

SQL> create table fruits (fruit_name varchar(20) unique, price_per_lb number);

Table created.

Then we inserted multiple rows into the table in one statement.

SQL> insert into fruits
    select 'Apple', 2 from dual
        union all
    select 'Banana', 1 from dual
        union all
    select 'Cherry', 5 from dual
;
  2    3    4    5    6    7
3 rows created.

SQL> commit;

Commit complete.

Let's see the result.

SQL> select * from fruits;

FRUIT_NAME           PRICE_PER_LB
-------------------- ------------
Apple                           2
Banana                          1
Cherry                          5

Currently, the table contains 3 items. Without case-insensitive unique constraint, we can add a duplicate item to it.

SQL> insert into fruits values ('aPPle', 4);

1 row created.

SQL> select * from fruits;

FRUIT_NAME           PRICE_PER_LB
-------------------- ------------
Apple                           2
Banana                          1
Cherry                          5
aPPle                           4

Does it look a little odd? To me, yes. At least, I think it's unreasonable to see two basically identical item. So I rolled it back.

SQL> rollback;

Rollback complete.

Solution

To prevent case-insensitive duplicate values, we can use UPPER() function to turn all values of the column into upper-cased ones in the index. Of course, you can also use LOWER() to make the letter-case of all characters uniform.

If you were considering to use ALTER TABLE to add this constraint, you should be disappointed.

SQL> alter table fruits add constraint fruit_name_ci unique (upper(fruit_name));
alter table fruits add constraint fruit_name_ci unique (upper(fruit_name))
                                                        *
ERROR at line 1:
ORA-00904: : invalid identifier

It throws error ORA-00904 to alert that there's no column named UPPER(FRUIT_NAME).

The right way is to use a CREATE INDEX statement.

SQL> create unique index fruit_name_ci on fruits(upper(fruit_name));

Index created.

Actually, it's a function-based index.

Let's try to insert the same rows into the table again.

SQL> insert into fruits values ('aPPle', 4);
insert into fruits values ('aPPle', 4)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.FRUIT_NAME_CI) violated

We saw ORA-00001. It's the expected behavior, there's no room for another same case-insensitive strings. This is how we create a case-insensitive unique index.