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 14:19:20 GMT
I'd suggest that you modify it a bit.

You probably don't want to lose case on the raw inserted data.
Case adds meaning to the content. If everything was in upper case, it would
seem like we're always SHOUTING. (Just an example.)

But the idea makes sense. 

What I would suggest is that you use an insert trigger to attempt to add a
lower case version of the input in to a second column which has the unique
key. If it fails then you know your first insert should fail.
So if you first enter "Harry Potter" then if you try to enter "harry Potter"
it would fail.

But hey! What do I know? ;-)
Sure you're increasing your storage space, however, you can't have your cake
and eat it too. 



> -----Original Message-----
> From: dmclean62@comcast.net [mailto:dmclean62@comcast.net]
> Sent: Thursday, August 24, 2006 7:32 AM
> To: Derby Discussion
> Subject: Re: Unique case-insensitive constraint on a varchar column
> 
> My recommendation is that you somehow force all data stored in that column
> to the same case. It may not be a perfect solution but then, this problem
> strikes me as rare enough that there may not be a better one.
> 
> Donald
> 
>  -------------- Original message ----------------------
> From: Ziad Mansouri <zmansour@yahoo.com>
> > 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');



Mime
View raw message