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
