db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: Unique constraints on multiple fields with one nullable
Date Thu, 27 Oct 2005 18:44:20 GMT
Michael J. Segel wrote:

> On Thursday 27 October 2005 11:01, Rick Hillegas wrote:
> 
>>Thanks, Michael. You are correct, Derby, like DB2, finesses this issue
>>by not allowing nullable columns in unique constraints. I have closed
>>this bug.
>>
>>Cheers,
>>-Rick
>>
> 
> NP,
> 
> 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.

> 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.


Mime
View raw message