Re: Forcing case insensitivity

I have just recently spoken to our DBA and he had similar bad news. The problem is that you cannot do the following...

create table x(
  y varchar2(2000),
  constraint unq_ci_y unique(upper(y))
);

In light of this limitation, there are several alternatives...

1. Use a stored procedure to first check for the existence of a case insensitive record using

   select count(y) into v_cnt from x where upper(y) = upper(p_y);

   And then if v_cnt is 0 proceed with the insert.

   This sort of thing is required when you need to store records    that need to be unique yet blind to case. Oracle's unique    constraint considers 'ABC', 'abc', 'aBc',... all to be    different and thus inserts will succeed. However, this may    not always be what you want. And casting to uppercase may    not be a valid solution if you want to store the information    as provided and not in upper case. This is definately true    in my application where I need to store the names of variables    in a table and do so WYSIWYG-like. The variables 'n' and 'N'    are the same thing so one must be rejected (hopefully by a    unique constraint). I want to store either 'n' or 'N' as    supplied, but not both. So I resort to the stored procedure    to do Oracle's work for it.

2) The other solution is similar and involves the use of triggers

   if you are in a anti-stored-procedure shop.

Either way, it is an oversight on Oracle's part to assume that developers have no need to be able to reject a record based on case rather than just casting to upper or lower and accepting it thus for storage.

Steve Catmull wrote:
>
> Is there a way to force a database or named source to force case
> insensitivity? The reason that I ask, is that I used to work with a
> personal version of SQL Anywhere and it had the option to force case
> insensitiviy when the database file was created.
>
> I talked with the DBAs in my company, but nobody seemed to be familiar
> with any method. They all thought that the SELECT statements would have
> to be modified to use the upper function. This is not too feasible
> because I am dealing with an client app that will not offer that type of
> configurability.
>
> By the way, the Oracle versions in question are 7.3.3 and 8.0.x.
>
> Thanks,
>
> Steve Catmull
> IHC
Received on Sun Jul 18 1999 - 12:32:17 CDT