MySQL falsely allowing duplicate entries when one of the fields involved is NULL
mysql, mysqli, php, sql
Solution
You can't compare NULL's (if you compare anything with NULL even NULL=NULL the results is always `FALSE`) this behavior is documented in MySQL ref.
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.
So I think the only way is define columns NOT NULL or handle this issue in a trigger.
Problem
Using InnoDB/MySQLi, I have a simple table: `mytable`. The table has four fields: `id` (primary, auto_inc), `field1`, `field2`, `field3`. All of them are `BIGINT` and, except for `id`, can be `NULL`. I have added a unique constraint like so: ``` ALTER TABLE mytable ADD UNIQUE INDEX(field1,field2,field3); ``` However, I am perfectly able to add the following rows without any error being generated. I would like for this to generate a 'duplicate' error, but it doesn't: ``` INSERT INTO mytable VALUES (NULL,3,NULL) INSERT INTO mytable VALUES (NULL,3,NULL) ``` It only generates a 'duplicate' error if all of the fields have non-NULL values - e.g., ``` INSERT INTO mytable VALUES (2,3,4) INSERT INTO mytable VALUES (2,3,4) ``` How can I tell MySQL to generate 'duplicate' errors even if one (or more) of the fields have `NULL` values? EDIT: This was previously added as a "bug" to MySQL: http://bugs.mysql.com/bug.php?id=25544