MySQL inserts duplicate records despite unique index

Okay, I thought I knew quite a bit about databases, so I did not expect any surprises when I added a unique index to my MySQL table. For those who do not know what a unique index is:

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. – MySQL manual

After adding the index the table looked like this:

CREATE TABLE `table` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  UNIQUE KEY `a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

And I was trying to insert or increment values in it like this:

INSERT INTO `table` (a,b,c) VALUES (1,NULL,3),(1,NULL,3) ON DUPLICATE KEY UPDATE c=c+VALUES(c);

I expected to get the following result:

mysql> select * from `table`;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | NULL |    6 |
+------+------+------+
1 row in set (0.00 sec)

But it did not do that! I got this result instead:

mysql> select * from `table`;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | NULL |    3 |
|    1 | NULL |    3 |
+------+------+------+
2 rows in set (0.00 sec)

I was clueless until I read the documentation of MySQL a little better:

This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. – MySQL manual

Especially the part where it says: “does not apply to NULL values” makes things much, much clearer. 🙂 Note that Microsoft SQL Server behaves different (they way I expected). So keep this is mind when using a unique index in MySQL, because I certainly did not expect this behavior!

Share

Leave a Reply

Your email address will not be published. Required fields are marked *