Bug #7479 Duplicate entries allowed with UNIQUE index when values are NULL
Submitted: 22 Dec 2004 12:18 Modified: 18 Oct 2005 5:54
Reporter: Frederic linot Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.21-standard OS:Linux (linux)
Assigned to:

[22 Dec 2004 12:18] Frederic linot
You can insert duplicate entries althought there is a UNIQUE index declared.
(when you manipulate NULL values)

The documentation does not talk about this specificity... 

How to repeat:
`a` INT,
`b` INT,
INDEX ( `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 );
[22 Dec 2004 12:55] Victoria Reznichenko
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

From the MySQL manual:

In MySQL, a UNIQUE index is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is allowed to contain NULL values, it can contain multiple NULL values. This exception does not apply to BDB tables, for which indexed columns allow only a single NULL.
[18 Oct 2005 5:54] Sergei Golubchik
see also BUG#6829