List:General Discussion« Previous MessageNext Message »
From:J. Ceferino Ortega Date:February 1 2002 8:16am
Subject:RE: Bug in UNIQUE?
View as plain text  
NULL is always not equal NULL in SQL, and if this UNIQUE index isn't NOT
NULL, then this behavior is right.

-----Mensaje original-----
De: Dan Nelson [mailto:dnelson@stripped] 
Enviado el: viernes, 01 de febrero de 2002 6:00
Para: Carsten Gehling
CC: Jude Insley; mysql@stripped; Paul DuBois
Asunto: Re: Bug in UNIQUE?

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

Before posting, please check:   (the manual)           (the list archive)

To request this thread, e-mail <mysql-thread98253@stripped>
To unsubscribe, e-mail
Trouble unsubscribing? Try:

Bug in UNIQUE?Unknown Sender31 Jan
  • Re: Bug in UNIQUE?Paul DuBois31 Jan
  • Re: Bug in UNIQUE?Carsten Gehling1 Feb
    • Re: Bug in UNIQUE?Dan Nelson1 Feb
      • RE: Bug in UNIQUE?J. Ceferino Ortega1 Feb