Unique case insensitive constraint in oracle database
oracle
Solution
The more efficient approach is the first approach. It's more efficient, though, only because the latter syntax doesn't work. You cannot, unfortunately, create a function-based constraint in the same way that you can create a unique index.
A unique constraint doesn't work
SQL> create table person (
2 first_name varchar2(10),
3 last_name varchar2(10)
4 );
Table created.
SQL> ALTER TABLE person ADD CONSTRAINT person_name_unique
2 UNIQUE(LOWER(first_name),LOWER(last_name));
UNIQUE(LOWER(first_name),LOWER(last_name))
*
ERROR at line 2:
ORA-00904: : invalid identifier
A unique function-based index, however, does work
SQL> create unique index idx_uniq_name
2 on person( lower(first_name), lower(last_name) );
Index created.
Problem
I have a `varchar` column in my table for url value. I have to make it unique across the records case-insensitively. I found 2 ways to achieve it. Create an unique index on the field. ``` create unique index <index_name> on <tablename>(lower(<column_name>)) ``` Add a unique constraint on the field as ``` ALTER TABLE person ADD CONSTRAINT person_name_unique UNIQUE(LOWER(first_name),LOWER(last_name)); ``` What is the efficient way to adopt from the above choices ?