Doh! Blindsided by an Oracle DBA, the only spec I could get my hands on is an old draft thing and I have to admit from that it looks like the correct behaviour.. NULL <> NULL and all that..
Thanks, Jon On Tue, 18 Jun 2002 10:54:15 -0500 Gerald Clark <[EMAIL PROTECTED]> wrote: > That is correct. > Use NOT NULL in the definition to force unique keys. > > [EMAIL PROTECTED] wrote: > > >>Description: > >> > > If a null column is used in a unique index then mysql fails to throw a > > key violation when multiple identical inserts (with nulls) are > > performed. > > > >>How-To-Repeat: > >> > > create table test (a int null, b int null); > > alter table test add unique index index_ab (a,b); > > insert into test (a,b) values(1,null); > > insert into test (a,b) values(1,null); > > insert into test (a,b) values(1,null); > > select * from test; > > +------+------+ > > | a | b | > > +------+------+ > > | 1 | NULL | > > | 1 | NULL | > > | 1 | NULL | > > +------+------+ > > 3 rows in set (0.00 sec) > > > > insert into test (a,b) values(1,1); > > insert into test (a,b) values(1,1); > > ERROR 1062: Duplicate entry '1-1' for key 1 > > > >>Fix: > >> > > No fix. > > > >>Submitter-Id: <submitter ID> > >>Originator: Jon Barker > >>Organization: > >> > > > > > >>MySQL support: none > >>Synopsis: NULLs don't work correctly in unique indexes. > >>Severity: serious > >>Priority: > >>Category: mysql > >>Class: > >>Release: mysql-3.23.41 (Official MySQL RPM) > >> > > > >>Environment: > >> > > > > > --------------------------------------------------------------------- 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
