db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Unique case-insensitive constraint on a varchar column
Date Thu, 24 Aug 2006 14:55:46 GMT
Ziad Mansouri wrote:
> Mates,
> 
> I'm trying to create a constraint, on a varchar
> column, which is case insensitive.  Here's the table:
> 
> create table categories (
> 	category_id int not null generated always as identity
> primary key,
> 	category varchar(64) not null
> 	);
> 
> I've tried:
> 
>    category varchar(64) not null unique
> 
> but then these two inserts are OK when I don't want
> them to be:
> 
> 	insert into categories(category) values('Test');
> 	insert into categories(category) values('test');
> 
> 
> I've also tried:
> 
> create table categories (
> 	category_id int not null generated always as identity
> primary key,
> 	category varchar(64) not null,
> 	constraint con check (category in (select
> lower(category) from categories))
> 	);
> 
> And I've tried before triggers and creating a separate
> index, as in:
> 
> create unique index i1 on categories
> (lower(category));
> 
> without any success.  Any help is much appreciated.

Hello Ziad,

You can write a trigger in Java to handle this case, but I'm not sure 
that is the easiest solution. However, if you do want to that, this is 
what you need to do:

1) Write the trigger action as a Java public static method.
    The class must be in Derby's classpath.
Then, in the database:
2) Define a function.
    It must be a function (the method must return a value), because
    procedures are not allowed in triggers.
3) Define the trigger on your table.
    This would call the function and pass the value being inserted.
    If the value, or a case variant, is already in the table an
    exception is thrown. This aborts the statement/insertion.

If you are sure this is what you want to do, I can provide more help if 
you need that. Please consult the manuals first; reference manual and 
the developer guide.
The Java method being called by the trigger, can query the table and 
convert its contents to lower/upper case and compare with the value 
being inserted.
If the insert should be denied, all you have to do is thrown an 
exception. This could be an application specific exception, or a 
standard SQLException with some descriptive text.

I'm sure you can optimize this if your data is getting big, as suggested 
in another mail. For instance create a secondary table, or maybe an 
index, to aid the lookup/search process. These helpers would keep all 
values in either lower or upper case.



Regards,
-- 
Kristian

> 
> Cheers,
> 
> 
> Z.
> 
> 
> 
> 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 


Mime
View raw message