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.