Re: Case-insensitive uniqueness?

A copy of this was sent to David Fetter <dfetter_at_shell4.ba.best.com> (if that email address didn't require changing) On 12 Nov 1999 05:02:45 GMT, you wrote:

>Kind people,
>
>I'd like to make sure that names are unique modulo case. One way of
>doing this that I thought would be elegant and understandable looks
>like this:
>
>CREATE TABLE foo (
> foo_id INTEGER NOT NULL PRIMARY KEY
>, foo_desc VARCHAR2(255) NOT NULL
>, UNIQUE(LOWER(foo_desc))
>)
>/
>
>Alas, this is not to be. Instead of something helpful, Oracle 8.0.5.1
>Enterprise Edition for Linux says:
>
>, UNIQUE(LOWER(foo_desc))
> *
>ERROR at line 4:
>ORA-00907: missing right parenthesis
>
>What's going wrong here, and how do I fix it?
>

In order to do this in 8.0.5 you will have to add a mirror column (foo_lowercase) and use a trigger to maintain it -- eg:

create or replace trigger x after insert or update on foo for each row
begin

   :new.foo_lowercase := :new.foo_desc; end;

and then create a unique constraint on foo_lowercase.

In Oracle8i, release 8.1, you can instead:

create unique index foo_idx on foo( lower( foo_desc ) );

>Thanks in advance for any help, tips, etc.
>
>Cheers,
>David.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 12 1999 - 06:51:13 CST