Make a UNIQUE constraint case insensitive

[mzhu]

Does any body know how to make a UNIQUE constraint case insensitive.

For example, I create a table like:

create table test (str varchar2(10) unique);

insert into test values ('abc');
insert into test values ('Abc');

I hope that the second insert above would suffer a unique constraint violated error.

Thanks for any help.

[svk]

function based index ??

[Shestakov]

Originally posted by svk
 function based index??

Function based index isn't UNIQUE constraint.
This is only index.

May be this (FBI) will works, but I dont' sure.

create unique index xxx on test(upper(str)) ....;

should test.

[svk]

that is what i meant.....

[noor]

Hello,

If you are using 8i Enterprise Edition, you can opt for the solution proposed by Shestakov... If you are using Standard Edition or an Oracle version other than 8i (or 9i), you can opt for a solution using a trigger...

Hope this helps

Regards