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 20:54:42 GMT
Michael J. Segel wrote:

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

Nope.

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

with that exact query, but that's not the definition of a unique constraint.

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

IS NULL predicate has nothing to do with the definition of a unique
constraint.

SQL standard says defines the search condition SC for a unique constraint as

sql> UNIQUE (SELECT id, name from FOO)

Then goes onto say

sql> The unique constraint is not satisfied if and only if
sql> EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )
sql> is True

Which means (in this case)

EXISTS (SELECT * FROM FOO WHERE NOT UNIQUE (SELECT id, name from FOO))


So to move onto the definition of the UNIQUE predicate

sql> If there are no two rows in T such that the value of each column in
sql> one row is non-null and is not distinct
sql> from the value of the corresponding column in the other row, then
sql> the result of the <unique predicate> is
sql> True; otherwise, the result of the <unique predicate> is False.

Got to love the double negatives!

So that is basically saying, since 'value of *each* column in one row is
non-null', that rows with null in them do not partake in the uniqueness
checking. At least that's my reading of it, maybe someone with more SQL
standard experience could chime in.

thus with our two rows of 6,NULL and 6,NULL then

UNIQUE (SELECT id, name from FOO) will return True,

so we are down to

EXISTS (SELECT * FROM FOO WHERE NOT TRUE)

EXISTS (SELECT * FROM FOO WHERE FALSE)

EXISTS (<empty-set>)

False

So the unique constraint is satisified from

sql> The unique constraint is not satisfied if and only if
sql> EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )
sql> is True


So multiple rows with the NULL in some columns and other values
identical are allowed.


Dan.

























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



Mime
View raw message