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