Re: Help! Case Insensitive Unique Indexes and PKs

In article <01be0836$ae2ea020$a12c6394_at_J00679271.ddc.eds.com>,   "Mark Powell" <Mark.Powell_at_eds.com> wrote:
> 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!
> >
>
>

Hi,

is there a specific reason for not using a numeric PK ?????

that should solve your case sensitivity problem

Regards
--
Oliver Willandsen
European Commission
http://europa.eu.int

All comments represent my own opinion and may not in any circumstance be regarded as stating an official position of the European Commission

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 05 1998 - 11:16:09 CST