> I have been mulling over this for a few days reading docs and going back
> forth with people on this, so I figured I would come here before writing
> bug report.
> First of all, I have tested this on 4.1.18, 5.0.16, and 5.0.22 within
> 9 and Mandrake Linux LE 2005 environments.
> For the sake of ease, I will just set up a small test table to assist me
> this question.
> > Create table test (
> x smallint not null,
> y char(5) default null,
> z char(10) not null default '',
> Unique Key `s`(x, y)
> ) ENGINE=MyISAM
> test> Select * from test;
> | x | y | z |
> | 1 | dan | yes |
> | 2 | joe | no |
> | 3 | NULL | maybe |
> | 3 | NULL | yes |
> Should the duplicate key checker be using the null-safe equals operator
> checking for duplicate unique keys? Since primary keys cannot have nulls
> them, then they are fine. This only happens when a unique key with a null
> value is encountered.
The behaviour you're seeing is correct according to the SQL Standard.
Given that NULL does not equal NULL, the tuples(x,y) for (3,NULL)
are different and thus valid.
Database Workbench - development tool for MySQL, and more!
Database development questions? Check the forum!