List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 5 2005 10:02am
Subject:Re: Unique Index on multiple columns that can contain NULL in MySQL
View as plain text  

> 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

Thread
Unique Index on multiple columns that can contain NULL in MySQLDennis Fogg5 May
  • Re: Unique Index on multiple columns that can contain NULL in MySQLHank5 May
  • Re: Unique Index on multiple columns that can contain NULL in MySQLJigal van Hemert5 May
  • Re: Unique Index on multiple columns that can contain NULL in MySQLMartijn Tonies5 May