Hi Sai,
This problem comes up frequently. One solution to it is an unimplemented
feature called computed columns (see DERBY-481). Hopefully, we can raise
the visibility of that issue.
In the meantime, I think that changing your INSERT and UPDATE code is a
good approach. You can give yourself some extra peace of mind by adding
a CHECK constraint which ensures that the normalized column has the
correct value and that there aren't any places in your application where
the wrong values are leaking in. E.g.:
ij> create table foo
(
a varchar( 50 ),
b varchar( 50 ),
check ( b = upper( a ) )
);
0 rows inserted/updated/deleted
ij> insert into foo values ( 'abc', 'ABC' );
1 row inserted/updated/deleted
ij> insert into foo values ( 'def', 'DEf' );
ERROR 23513: The check constraint 'SQL080116120009780' was violated
while performing an INSERT or UPDATE on table '"APP"."FOO"'.
Hope this helps,
-Rick
Sai Pullabhotla wrote:
Thanks Bryan,
I thought about it. But I'm not sure if it is THE BEST way. Any other folks
here have any ideas/comments?
Also, let us say, if we do go with two columns approach (one for Display
purpose and the other for storage purpose), is there a way I can set up
triggers on these tables to automatically put the lower/UPPER case values in
the internal column? I tried to create a BEFORE INSERT trigger, but did not
have any luck. I could not even get it to compile.
Or do you think it is best to change the code where the INSERT and UPDATE
statements are, rather than messing with triggers?
I appreciate any ideas/comments.
Sai Pullabhotla
Linoma Software
1409 Silver St
Ashland, NE 68003
(402) 944 4242 x 754
(800) 949 4696 x 754
-----Original Message-----
From: Bryan Pendleton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 15, 2008 5:38 PM
To: Derby Discussion
Subject: Re: Case-Insensitive Unique Constraint
Is there a way to create a unique constraint/index which ensures the
uniqueness of data IGNORING the case?
One idea would be to store the data twice, in two separate columns:
- in one column, store the data normally, in the case as provided
- in the other column, store the data in all upper case
Then create a unique index on the all-upper-case column.
thanks,
bryan
