Rick,
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 UPPER/LOWER. Thanks again for your help. Regards, Sai Pullabhotla President jMethods, Inc. Phone: +1 (402) 408-5753 Fax: +1 (402) 408-6861 www.jMethods.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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, -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 > > > >
