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 Bryan Pendleton <bp...@amberpoint.com> on 2008/01/16 00:38:26 UTC
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 <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