db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael J. Segel" <mse...@segel.com>
Subject Re: Unique constraints on multiple fields with one nullable
Date Fri, 28 Oct 2005 02:47:36 GMT
On Thursday 27 October 2005 15:54, Daniel John Debrunner wrote:

> > 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.
> Nope.
Junior, allow me to rephrase. With a Unique constraint in place, when I query 
against the tuple of the constraint, I should get at the most one row 
returned. This is the anticipated and implied result when the term unique is 

Now I looked down and read your response. You seem to be able to regurgitate 
text book language, yet fail to comprehend its meaning.

Lets simplify what you read....

You have a table, where there are no rows. You apply the constraint.
When you attempt an insert in to the table, the constraint is tested. That is 
to say that there is an inner select statement which will return true if no 
rows meeting the constraint is found. Note that the exact manner of how the 
constraint is applied is going to be database dependent.

When you attempt to insert another row that has a matching tuple, the 
constraint will return false because it will find a row that matches.

Now the problem. What happens when a column in the constraint is NULL?
Pay attention Junior, this is the important part....
A NULL value is a special case. Derby avoids this by not allowing NULL values 
in a column that has been defined by a constraint. Did you think that those 
who wrote Cloudscape did this by accident? KISS in action. (You do know the 
technical term KISS, right?)

In all that you wrote, you are in agreement with what I am saying until you 
hit a situation of a NULL.  I seriously doubt that you took the time to 
understand the analogy to Schrodinger's Cat.

This is why I suggested that you consider the implied meaning of a Unique 
constraint. When you tell a developer that you have a unique constraint on a 
row, you expect either the empty set, or one row back. Because of this, how 
Sybase handles a NULL value is a bug. And it can be very dangerous.  By 
looking at the implied meaning, you avoid Schrodinger's Cat. After all, 
what's the definition of the word unique? 

But hey, what do I know?  I'm old school. K&R created a near perfect 
language. ;-)

I'll just leave this in your capable hands. After all, you're paid to play 
here. I'm not.


Michael Segel
(312) 952-8175

View raw message