db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sai Pullabhotla" <Sai.Pullabho...@jMethods.com>
Subject RE: Case-Insensitive Unique Constraint
Date Thu, 17 Jan 2008 15:45:23 GMT


Thanks for the advice on using a CHECK constraint. It surely helps ensuring
the data integrity. 

Otherwise, I think this is very basic feature that every one would like to
see, and it would help if Derby can handle this automatically using the
Computed/Generated columns or allowing UNIQUE index creation using

Thanks again for your help. 


Sai Pullabhotla
jMethods, Inc. 
Phone: +1 (402) 408-5753 
Fax: +1 (402) 408-6861
-----Original Message-----
From: Richard.Hillegas@Sun.COM [mailto:Richard.Hillegas@Sun.COM] 
Sent: Wednesday, January 16, 2008 2:04 PM
To: Derby Discussion
Subject: Re: Case-Insensitive Unique Constraint

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,

Sai Pullabhotla wrote:
> Thanks Bryan, 
> I thought about it. But I'm not sure if it is THE BEST way. Any other
> 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
> the internal column? I tried to create a BEFORE INSERT trigger, but did
> 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:bpendleton@amberpoint.com] 
> 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

View raw message