Unique case insensitive constraint in oracle database

Question

I have a varchar column in my table for the URL value. I have to make it unique across the entire case insensitive entry. I found two ways to achieve this.

• Create a unique index in the field.

   create unique index <index_name> on <tablename>(lower(<column_name>)) 

• Add a unique constraint to the field as

   ALTER TABLE person ADD CONSTRAINT person_name_unique UNIQUE(LOWER(first_name),LOWER(last_name)); 

What is an effective way of taking from the above options?

Answer 1

A more effective approach is the first approach. However, this is more efficient just because the latter syntax does not work. Unfortunately, you cannot create a restriction based on functions in the same way as you can create a unique index.

Unique restriction does not 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 

However, a unique feature-based index works

 SQL> create unique index idx_uniq_name 2 on person( lower(first_name), lower(last_name) ); Index created.