> 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
> 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
> NULL will result in MAYBE.
> If you look at NULL as being a yet unknown variable it starts to make
> (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) ->
> 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
> 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 :-)
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL