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.