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)
From: "Michael J. Segel" <msegel@segel.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Unique constraints on multiple fields with one nullable
Date: Thu, 27 Oct 2005 14:26:23 -0500
User-Agent: KMail/1.8
References: <20051026223634.95863.qmail@web32507.mail.mud.yahoo.com>
 <200510271318.10684.msegel@segel.com> <43612004.6050505@debrunners.com>
In-Reply-To: <43612004.6050505@debrunners.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200510271426.24071.msegel@segel.com>

On Thursday 27 October 2005 13:44, Daniel John Debrunner wrote:

> > But Dan's reply is an interesting one.
> >
> > What Sybase did was create a Schrodinger's Cat.
> > (See http://whatis.techtarget.com/definition/0,,sid9_gci341236,00.html
> > for a definition... ;-)
> >
> > This is actually a bug in Sybase.
> > (Note: Bug because by allowing multiple tuples where n-1 of the
> > constraint columns match and the nth column is NULL, the guaranteed
> > uniqueness fails. [Where n = number of columns in the table constraint] )
>
> Is it a bug? NULL is not equal to NULL in SQL land, so 6,NULL is not
> equal to 6,NULL so uniqueness has not been violated.
>
Sigh.
Ok, so you want to play with cats?
What is NULL?

That was a rhetorical question. 

Here's a more practical approach and why its a bug.

By the definition of a UNIQUE TABLE CONSTRAINT, I should get only a single row 
returned when I query on the tuple that are part of the defined constraint.

Using our existing example, If I say the following:
SELECT * 
FROM foo 
WHERE id = 1
AND     name = "AAA";

I should expect that I will get back at the most 1 record.

Now why is Sybase a bug?
Suppose we had the following:

SELECT * 
FROM foo 
WHERE id = 1
AND name IS NULL;

How many rows will I get back?
(Again its rhetorical...)

Hint: If what Dan M says is true, I will not be guaranteed at the most one row 
returned. Hence the CONSTRAINT FAILED.

> > With respect to constraints in Derby...
> >
> > You really need to consider allowing NULLs in columns that are part of a
> > table constraint. In fact, you should really reconsider how you handle
> > constraints in general.
> >
> > But hey, what do I know?  Meow. ;-)
>
> Hmmmmm, since we are not mind readers, well I'm not, others may be, it's
> hard to know what you know and more importantly why you think
> constraints should be handled differently. Throwing out a comment such
> as 'In fact, you should really reconsider how you handle constraints in
> general.' doesn't really add any value to any discussion. Starting up a
> new discussion on the developer list on possible improvements to
> constraints would be great, even better would be contributing the
> improvements yourself.
>
> Dan.
Well, its a good thing you're not a mind reader. ;-)

With respect to constraints, under the current design, you have an 
inconsistency within Derby.

I can create a constraint on an index that is applied to a table, while I can 
not create the same constraint directly on the table. To me, thats a 
design/implementation fault. 

Again, this goes back to how you consider and handle constraints in the first 
place.

-G
-- 
Michael Segel
Principal
MSCC
(312) 952-8175