Unique case insensitive constraint in oracle database

Question

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.

1. Create an unique index on the field.

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

2. 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 ?

Answer 1

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.