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 (athena.apache.org: domain of Sai.Pullabhotla@jmethods.com
 designates 65.212.180.92 as permitted sender)
Message-Id: <200801152322.m0FNMkSf007668@pyramid-03.kattare.com>
Reply-To: <Sai.Pullabhotla@jMethods.com>
From: "Sai Pullabhotla" <Sai.Pullabhotla@jMethods.com>
To: <derby-user@db.apache.org>
Subject: Case-Insensitive Unique Constraint 
Date: Tue, 15 Jan 2008 17:22:33 -0600
Organization: jMethods, Inc.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AchXzYFfxigJIndOTL6DoktsBaqqpA==

Hello every one, 

I'm in the process of porting an application from HSQLDB to Derby. I ran
into an issue with defining unique constraints on character (CHAR and
VARCHAR) data IGNORING the case. HSQLDB has a proprietary data type called
"varchar_ignorecase" and we have used that extensively. However, there is no
such thing in Derby that could find. So my questions are: 

Is there a way to create a unique constraint/index which ensures the
uniqueness of data IGNORING the case? 

What I mean by above is - trying to insert "derby" when "Derby" already
exists in the table should produce a constraint violation error. 

I've gone through the documentation and Googled for several hours, but could
not find anything to accomplish this. 

If there is no direct way to do this, what would be the best
alternative/workaround? 

Any help would be greatly appreciated. 

Regards, 
Sai Pullabhotla
President
jMethods, Inc. 
Phone: +1 (402) 408-5753 
Fax: +1 (402) 408-6861
www.jMethods.com