Xaprb

There’s a really important difference between a unique index (MySQL’s answer to a “unique constraint”) and a primary key in MySQL. Please take a look at this:

CREATE TABLE `t` (
  `a` int,
  `b` int,
  `c` int,
  UNIQUE KEY `a` (`a`,`b`)
)

The combination of columns a, b should uniquely identify any tuple in the table, right?

select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
+------+------+------+

Wrong. Our arch-enemy NULL messes things up again:

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. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL

MySQL doesn’t let you define a primary key over nullable columns, for this reason. This is as of version 4.0, I believe — I recall that in version 3.23 there was nothing special about a primary key; it was just a unique non-nullable index named PRIMARY.