You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Sai Pullabhotla <Sa...@jMethods.com> on 2008/01/16 00:22:33 UTC

Case-Insensitive Unique Constraint

Hello every one, 

I'm in the process of porting an application from HSQLDB to Derby. I ran
into an issue with defining unique constraints on character (CHAR and
VARCHAR) data IGNORING the case. HSQLDB has a proprietary data type called
"varchar_ignorecase" and we have used that extensively. However, there is no
such thing in Derby that could find. So my questions are: 

Is there a way to create a unique constraint/index which ensures the
uniqueness of data IGNORING the case? 

What I mean by above is - trying to insert "derby" when "Derby" already
exists in the table should produce a constraint violation error. 

I've gone through the documentation and Googled for several hours, but could
not find anything to accomplish this. 

If there is no direct way to do this, what would be the best
alternative/workaround? 

Any help would be greatly appreciated. 

Regards, 
Sai Pullabhotla
President
jMethods, Inc. 
Phone: +1 (402) 408-5753 
Fax: +1 (402) 408-6861
www.jMethods.com 



RE: Case-Insensitive Unique Constraint

Posted by Sai Pullabhotla <Sa...@jMethods.com>.
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.

Regards, 

Sai Pullabhotla
President
jMethods, Inc. 
Phone: +1 (402) 408-5753 
Fax: +1 (402) 408-6861
www.jMethods.com 

-----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




Re: Case-Insensitive Unique Constraint

Posted by Mark Thornton <mt...@optrak.co.uk>.
Mark Thornton wrote:
> Sai Pullabhotla wrote:
>> 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.   
> Another alternative would be to allow a collation to be specified when 
> creating an index.
>
> Mark Thornton
>
Something like this:

create unique index roadnamesindex on roadnames(name collate 
'en_UK_primary', featureid)

(Borrowed from an in house database which supports that statement.)

Mark Thornton


Re: Case-Insensitive Unique Constraint

Posted by Mark Thornton <mt...@optrak.co.uk>.
Sai Pullabhotla wrote:
> 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. 
>   
Another alternative would be to allow a collation to be specified when 
creating an index.

Mark Thornton


RE: Case-Insensitive Unique Constraint

Posted by Sai Pullabhotla <Sa...@jMethods.com>.

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: 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,
-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: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
>
>
>
>   


Re: Case-Insensitive Unique Constraint

Posted by Rick Hillegas <Ri...@Sun.COM>.
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: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
>
>
>
>   


RE: Case-Insensitive Unique Constraint

Posted by Sai Pullabhotla <ps...@linoma.com>.
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: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




Re: Case-Insensitive Unique Constraint

Posted by Bryan Pendleton <bp...@amberpoint.com>.
> 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