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 (nike.apache.org: local policy)
Reply-To: <psai@linoma.com>
From: "Sai Pullabhotla" <psai@linoma.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: Case-Insensitive Unique Constraint
Date: Wed, 16 Jan 2008 10:01:22 -0600
Organization: Linoma Software
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AchYVL3Os4pRwpNSRs2Ma6GaIYKj4gAA51ww
In-Reply-To: <478D43F2.70601@amberpoint.com>
Message-Id: 
 <20080116160124.OHIS27517.ispmxaamta08-gx.windstream.net@SaiLinoma>


Thanks Bryan, 

I thought about it. But I'm not sure if it is THE BEST way. Any other folks
here have any ideas/comments? 

Also, let us say, if we do go with two columns approach (one for Display
purpose and the other for storage purpose), is there a way I can set up
triggers on these tables to automatically put the lower/UPPER case values in
the internal column? I tried to create a BEFORE INSERT trigger, but did not
have any luck. I could not even get it to compile. 

Or do you think it is best to change the code where the INSERT and UPDATE
statements are, rather than messing with triggers? 

I appreciate any ideas/comments. 

 
Sai Pullabhotla
Linoma Software 
1409 Silver St
Ashland, NE 68003 
(402) 944 4242 x 754
(800) 949 4696 x 754
-----Original Message-----
From: Bryan Pendleton [mailto:bpendleton@amberpoint.com] 
Sent: Tuesday, January 15, 2008 5:38 PM
To: Derby Discussion
Subject: Re: Case-Insensitive Unique Constraint

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

One idea would be to store the data twice, in two separate columns:
  - in one column, store the data normally, in the case as provided
  - in the other column, store the data in all upper case

Then create a unique index on the all-upper-case column.

thanks,

bryan