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
