MySQL: is primary key unique by default?

Question

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?

Answer 1

Primary key is always unique in every SQL. You dont have to explicitly define it as UNIQUE.

On a side note: You can only have onePrimary key in a table and it never allows null values. Also you can have only one primary key constraint in the table(as the point of creating a primary key is to uniquely identify the row in your table) but you can more than one unique key constraint in your table.

Example:

An employee details table having EmpID as Primary key and EmpPhoneNo as unique key.

Answer 2

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

Answer 3

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 |
+--------+---------------------------…+