13

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 ?

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Awesome
  • 5,689
  • 8
  • 33
  • 58
  • 1
    This is a pretty comprehensive explanation of the difference, without commenting explicitly on efficiency: http://stackoverflow.com/a/7522004/110933 – davek Nov 20 '14 at 06:02
  • 1
    A unique constraint use a unique index under the hood, there is no difference betwen them, the only difference is the syntax. – krokodilko Nov 20 '14 at 06:13
  • 3
    I would prefer the constraint; the difference is in semantics. A future developer might consider the constraint as a form of documentation: "this must be case-insensitive unique". A unique index, OTOH, might be construed as being merely created for performance purposes: "this column happens to always be case-insensitive unique" – Jeffrey Kemp Nov 20 '14 at 07:45

2 Answers2

15

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.
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 1
    Thanks, so, if we create a unique function-based index, then we do not need to / we should not add a unique constraint on the column? – user1116119 Jan 23 '17 at 05:13
  • @user1116119 - You can't create a unique constraint on the function value, just a unique function-based index. Depending on your Oracle version, you should also be able to create a computed column that is `lower(first_name)` and create a unique constraint on that. – Justin Cave Jan 25 '17 at 03:02
  • One thing it's worth adding - you can't reference the unique index directly in an ON CONFLICT ON CONSTRAINT, but you can reference the condition (e.g. ON CONFLICT (LOWER(first_name), LOWER(last_name)). Tripped me up today, and this is the first result on Google for the question, so figured I'd mention it... – Adam Bethke Jan 17 '19 at 17:03
0
  • 1 is possible and gives error for duplicate.
  • 2 is not possible. (function is not possible in constraint)
RoutesMaps.com
  • 1,628
  • 1
  • 14
  • 19