db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ziad Mansouri <zmans...@yahoo.com>
Subject Unique case-insensitive constraint on a varchar column
Date Thu, 24 Aug 2006 02:35:13 GMT
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.

Cheers,


Z.






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

Mime
View raw message