db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: Unique case-insensitive constraint on a varchar column
Date Thu, 24 Aug 2006 15:36:40 GMT
Here's a simple way to do this...
Create an index on the column. It doesn't have to be unique.
Create a before insert trigger that calls a function.
The function just needs to execute a simple query:
"SELECT COUNT(*) FROM test WHERE lower(text) = ? "

In my test, I created a table test and the VARCHAR column was called text.
If the count is > 0, then you know that the value is in the table and you
throw the exception.



> -----Original Message-----
> From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
> Sent: Thursday, August 24, 2006 9:56 AM
> To: Derby Discussion
> Subject: Re: Unique case-insensitive constraint on a varchar column
> 
> 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