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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > 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
