From: Dan Nelson
Date: February 1 2002 4:59am
Subject: Re: Bug in UNIQUE?
List-Archive: http://lists.mysql.com/mysql/98253
Message-Id: <20020201045934.GC98085@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Feb 01), Carsten Gehling said:
> > >The SQL below illustrates what I believe is a bug in MySQL up to
> > >and including 3.23.47. Essentially I need a unique key where one
> > >or more of the component fields of the unique key can be NULL.
> > >What seems to happen is that you can add "duplicate" rows if the
> > >value is NULL.
> >
> > This is how UNIQUE indexes work in MySQL.  All values except NULL
> > must be unique.  If you use a PRIMARY KEY instead, this won't
> > happen. Of course, the reason it won't happen is that PRIMARY KEY
> > disallows NULL entirely.
> 
> I thought that UNIQUE indexes constituted what is known in the relational
> model as a "candidate key" and, AFAIR, none of the members in a candidate
> key may contain NULL values.

a UNIQUE index must also have the NOT NULL attribute to be a candidate
key.  Otherwise it's just an index.

-- 
	Dan Nelson
	dnelson@stripped