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 …
[Read more]