Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
  b=ZcZkkpyVQYrgKKXYOCR6WXrZZMXe/YefkPA15KMIcszngEx1vQjH4hFVGLrl9WeDXf98QR/Mj49O56tJNZhVrm02hEzxZeFebAVVE3w+4RPufmfDTbXBsasYrwAz1TKZKLo9Dy6q8D3jvN/UYDzvu1Bvd4ihG5J8tHRpqNyYd8k=
  ;
Message-ID: <20060824023513.93164.qmail@web53213.mail.yahoo.com>
Date: Wed, 23 Aug 2006 19:35:13 -0700 (PDT)
From: Ziad Mansouri <zmansour@yahoo.com>
Subject: Unique case-insensitive constraint on a varchar column
To: derby-user@db.apache.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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