Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: unique index on varchar2 column/805

Re: unique index on varchar2 column/805

From: Philipp Florschutz <pf_at_gmx.net>
Date: 30 Jun 1999 14:32:47 GMT
Message-ID: <7ld9qf$ei2$1@black.news.nacamar.net>


Mark Malakanov <markmal_at_sprint.ca> wrote:

> Why wouldn't you store this column lowercased at all?

It is up to our users to decide about lower and upper case. We want to keep it like it was entered but we need to keep it unique regardless of case.

> the simple conversion trigger can help you.

Yes, from the DB side we will have to use a trigger and maybe a hidden column. Unfortunately this is not enough, as we need to make changes in the application to get the select like it worked on SQL server.

> What do you mean in " This works for insert but not for select as we do not
> want
> to use 'like' instead of '=' " ?

You have a value of 'Blablub' for column "name" in the DB. Now the query coming in is
  select ... where name='blablub'
which will fail, according to SQL standard. However, on SQL server it delivers a result.
We cannot do anything here, as everything requires - changes in the application:

> You can use "=" any way.

Not, if the strings do not match in case, as I experienced. Hope I am not wrong here :)

Well, I talked to more Oracle people now, and I know there is no simple way for doing it, unfortunately. So, we will do a hack...

Thank you for your comments, Philipp Received on Wed Jun 30 1999 - 09:32:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US