Xaprb

Stay curious!

The difference between a unique index and primary key in MySQL

with 2 comments

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.

Written by Xaprb

September 12th, 2009 at 5:07 pm

Posted in SQL

Tagged with , , ,

«
»

2 Responses to 'The difference between a unique index and primary key in MySQL'

Subscribe to comments with RSS

  1. Hi!

    It’s worth pointing out that although it seems counterintuitive, this conforms to the SQL standard. It works exactly like this in Oracle.

    I am not sure about the 3.23 behaviour, but as long as i have been using MySQL, it allows DDL like this:

    CREATE TABLE T (a int, b int, primary key(a,b))

    Now the strange thing is that a and b appear nullable, but when you do a SHOW CREATE TABLE T, they turn out to be not nullable – this is automatic smarts that one may or may not like (it actually occurs even if you explcitly declare the columns as NULL)

    kind regards,

    Roland

    Roland Bouman

    12 Sep 09 at 6:52 pm

  2. I (unfortunately) have used 3.23 quite a bit. A primary key was UNIQUE NOT NULL back then as it is now. I think in early version that might not have been the case.

    Given that null does not describe a value so much as an unknown value:
    mysql> select ‘Null equals Null’ from dual where null=null;
    Empty set (0.00 sec)

    mysql> select ‘Null does not equals Null’ from dual where null!=null;
    Empty set (0.00 sec)

    I don’t have a problem with how unique is used in RDMS, but then again I am in the pro-null camp. Perhaps I should seek psychiatric evaluation?

    Rob Wultsch

    13 Sep 09 at 3:26 am

Leave a Reply