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:[EMAIL PROTECTED]] Enviado el: viernes, 01 de febrero de 2002 6:00 Para: Carsten Gehling CC: Jude Insley; [EMAIL PROTECTED]; 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 [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php