MySQL unique key vs primary key constraints

Category:

Tutorials > Database Management Systems > MySQL > Tips and Tricks

MySQL unique and primary keys serve to identify one and only one row in a table. However, there are some differences that we are going to look at in this tutorial. All the conclusions were drawn using MySQL 5.0.67 server.

MySQL unique keys can be NULL whereas primary keys cannot be:

mysql> CREATE TABLE uni_pri (id_primary INT PRIMARY KEY NULL DEFAULT NULL, id_unique INT NULL UNIQUE KEY DEFAULT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW CREATE TABLE uni_pri;
+---------+--------------------------+
| Table   | Create Table             
+---------+--------------------------+
| uni_pri | CREATE TABLE "uni_pri" (
  "id_primary" int(11) NOT NULL default '0',
  "id_unique" int(11) default NULL,
  PRIMARY KEY  ("id_primary"),
  UNIQUE KEY "id_unique" ("id_unique")
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO uni_pri (id_primary, id_unique) VALUES (NULL, NULL);
ERROR 1048 (23000): Column 'id_primary' cannot be null
mysql> INSERT INTO uni_pri (id_primary, id_unique) VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO uni_pri (id_primary, id_unique) VALUES (2, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM uni_pri;
+------------+-----------+
| id_primary | id_unique |
+------------+-----------+
|          1 |      NULL |
|          2 |      NULL |
+------------+-----------+
2 rows in set (0.00 sec)

There can be only one Primary key in a table but one or more unique keys:

mysql> CREATE TABLE uni_pri (id_primary1 INT PRIMARY KEY, id_primary2 INT PRIMARY KEY, 
id_unique1 INT NULL UNIQUE KEY, id_unique2 INT NULL UNIQUE KEY);
ERROR 1068 (42000): Multiple primary key defined
mysql> CREATE TABLE uni_pri (id_primary1 INT PRIMARY KEY, id_unique1 INT NULL UNIQUE KEY, id_unique2 INT NULL UNIQUE KEY);
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW CREATE TABLE uni_pri;
+---------+--------------------------+
| Table   | Create Table             
+---------+--------------------------+
| uni_pri | CREATE TABLE "uni_pri" (
  "id_primary1" int(11) NOT NULL,
  "id_unique1" int(11) default NULL,
  "id_unique2" int(11) default NULL,
  PRIMARY KEY  ("id_primary1"),
  UNIQUE KEY "id_unique1" ("id_unique1"),
  UNIQUE KEY "id_unique2" ("id_unique2")
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------+
1 row in set (0.00 sec)

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

Thanks for Post & give me knowledge about this Posted by: vinay P on Jan 05, 2012

In InnoDB, there are huge differences between the PRIMARY KEY and a UNIQUE key. Performance differences, implementation differences, etc.

Many of the gory details are described here:
http://mysql.rjweb.org/doc.php/myisam2innodb

(Sorry if this is supposed to be a MySQL-only web site. The world is moving away from MyISAM toward InnoDB.) Posted by: Rick J on Feb 27, 2012

You state that you can only have one primary key. But how come i can create a table like:
CREATE TABLE `myTable` (
`ID1` INT UNSIGNED NOT NULL,
`ID2` INT UNSIGNED NOT NULL,
`value` INT NOT NULL,
PRIMARY KEY(ID1, ID2)
);

Wouldn't that be considered having 2 primary keys? Posted by: Tim M on Apr 04, 2012

Tim, in your create table statement you're creating one primary key with two columns in it. In that case, ID1 (or ID2) could have repeated values but each row must have a unique combination of (ID1, ID2). Posted by: Matthew on May 31, 2012

What is the difference between:

PRIMARY KEY(ID1, ID2)
and
CONSTRAINT IDs PRIMARY KEY(ID1, ID2)

Just wondering... Posted by: Jafet on Sep 13, 2012

leave a comment on tutorial leave a comment