> From: "Dennis Fogg" > > I'm getting lots of duplicate rows even though I have a > > unique index defined over multiple columns. > > The issue is that multiple NULL values are allowed, > > even when some values are not null. > > This could be as specified by the SQL standard, > > but it's certainly confusing for the developer. > > It is confusing. You have to 'grok' NULL "values" to understand the problems > that may arise. > In Boolean logic you're used to two distinct values TRUE and FALSE. The > introduction of NULL actually introduced a second outcome of comparison > operators and functions. Since NULL represents "unknown", comparing NULL to > NULL will result in MAYBE. > If you look at NULL as being a yet unknown variable it starts to make sense: > (x = 2) : maybe true, maybe false, depending on the value of 'x'. > (x != 2) : maybe true, maybe false, depending on the value of 'x'. > In MySQL the outcome MAYBE is represented by NULL, so (NULL = NULL) -> NULL; > and (NULL != 2) -> NULL; (NULL * 1) -> NULL; etcetera. > > Regarding indexes, there doesn't seem to be any logic involved, other than > "what the standard says". UNIQUE indexes may have multiple NULL values > (excepting BDB tables). A PRIMARY key is defined as a combination of UNIQUE > and NOT NULL.
Since when does the standard handle Indices? It's about time that MySQL gets Unique Constraints - these are defined by the SQL standard :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
