Re: Help! Case Insensitive Unique Indexes and PKs

The comparison operators, =, >, <, !=, and so on perform case sensitive comparisons of character values because 'A' is not equal to 'a'. Naturally, the value 'A' does not fit into a btrieve structure at the same point as 'a'.

One way to solve the type of problem you seem to have encountered would be to store the data in only one case. A check constraint could be defined on the table column to do this, then the where clause could make use of the upper() or lower() function against the bind variable to insure proper comparison:

select *
from your_table
where col1 = upper('variable');

If you can not store the data in one case in the target column then my next suggestion is to add another column, col2, to the table and to use a before insert and before update trigger to propagate col1 to it using an upper function. Then index col2. You have now built an index where primary key 'A' and primary key 'a' are stored as the same value (since we made them equal). By querying against col2 you should be able to find your data.

If you can not add another column and triggers then all I can suggest is to write queries using a union or union all like:

select *
from your_table
where col1 = upper(variable')
union all
select *
from your_table
where col1 = lower(variable')
order by 1;

I hope one of these ideas is of use to you.

Douglas White <douglas.white_at_dial.pipex.com> wrote in article <71psv0$41e$1_at_plug.news.pipex.net>...
> Is it possible to create unique indexes and primary keys that are case
> insensitive ?
> As I understand it, Oracle only supports case sensitive unique indexes
and
> PKs.
> Sorry if this is a basic question.
> Any comments or suggestions would be extremely welcome!
>
Received on Wed Nov 04 1998 - 15:02:10 CST