Create Case-Insensitive Unique Index in Oracle

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.

Solutions

To implement an unique and case-insensitive index in Oracle database, we have some ways to do it.

Function-Base Index

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

COLLATE BINARY_CI

Since release 12.2, we have column-level collation to be used. Therefore, to be sorted in a case-insensitive manner, we have to specify COLLATE BINARY_CI to modify the indexed column.

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

Index created.

Let's test the unique index by inserting an existing value with some variations in letter cases.

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

We did it.

For accent-insensitive collation, we should use BINARY_AI instead.