The difference between a unique index and primary key in MySQLSat, Sep 12, 2009 in Databases
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.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.