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: neutral (herse.apache.org: local policy)
Message-ID: <45803FE3.40102@sbcglobal.net>
Date: Wed, 13 Dec 2006 10:01:07 -0800
From: Mike Matrigali <mikem_app@sbcglobal.net>
Reply-To: mikem_app@sbcglobal.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: unique columns must be nullable ?
References: <loom.20061212T050617-146@post.gmane.org>
 <457EEDCD.2070301@sbcglobal.net> <457F294A.7000300@nuix.com>
 <D112D391-FF4A-423C-8FD7-F920C992F832@SUN.com>
 <457F4947.3050408@sbcglobal.net>
 <B9F83BB0-66D1-4585-87D2-8940DB08DABF@SUN.com>
In-Reply-To: <B9F83BB0-66D1-4585-87D2-8940DB08DABF@SUN.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> 
> 
> What would break if we "simply" change this such that NULL always  
> compares not equal with all other values including NULL?
> 
> Aside from a number of test cases for this particular case...
> 
> Craig
>
"not equal" would not work for the btree.  It needs a reproducible
ordering for all key columns so that it can reliably find a particular
key value - this is a recovery requirement.

If someone is interested in supporting this, I think the easiest 
solution which would not affect current index performance would be
to internally create "non-unique" indexes for nullable constraints
and at insert time check if the key exists in the btree if it is
not null and throw the exception from higher up than it is now.

One could somehow special case this logic into the btree, but my
worry is that the code then slows down/complicates all unique indexes 
for this one special case of allowing 2 nulls.