'MySQL: is primary key unique by default?

If I define a column as a primary key in MySQL, is it also unique key by default or do I need to also define it as unique key (in case I want it to be unique)?

I saw this question What is the difference b/w Primary Key and Unique Key that explain the difference between the two, but doesn't exactly answer my question.

Does PK is UK by default or I need to explicitly define it?

mysqlprimary-keyunique-key


Solution 1:[1]

Primary key is always unique by definition. Not only in MySQL. So you don't need any additional unique key.

Solution 2:[2]

Note that composite keys may lead to confusion : indeed a primary key can be a composite key, and DESCRIBE will show all of the composite key components as primary keys :

> DESCRIBE foobar;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| column_A             | int(10) unsigned | NO   | PRI | NULL    |       |
| column_B             | int(10) unsigned | NO   | PRI | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+

However SHOW CREATE TABLE will show the reality :

> SHOW CREATE TABLE foobar;
+--------+---------------------------…+
| Table  | Create Table              …|
+--------+---------------------------…+
| foobar | CREATE TABLE `foobar` (
  `column_A` int(10) unsigned NOT NULL,
  `column_B` int(10) unsigned NOT NULL,
  PRIMARY KEY (`column_A`,`column_B`),
  KEY `column_B` (`column_B`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------…+

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jakub Matczak
Solution 2 Skippy le Grand Gourou

Related Questions