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?

possible duplicate of [What is the difference b/w Primary Key and Unique Key](http://stackoverflow.com/questions/2973420/what-is-the-difference-b-w-primary-key-and-unique-key) — Akhil, Jun 18 '15 at 09:48
@Akhil - I saw this question in the link, 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. — Roee Gavirel, Jun 18 '15 at 13:03
possible duplicate of [difference between primary key and unique key](http://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key) — James Z, Jun 18 '15 at 19:37

score 72 · Accepted Answer · edited Jan 22 '20 at 17:01

72

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.

edited Jan 22 '20 at 17:01

Serghei Niculaev

513
7
17

answered Jun 18 '15 at 09:45

Rahul Tripathi

168,305
31
280
331

4

another point : a table can have **only one PRIMARY KEY** constraint but it can have as many as you want UNIQUE KEY constraints – Bacteria Jun 18 '15 at 09:50
2

Actually there are more differences between unique and primary key, like primary key cannot be NULL. But that's not the question about. – Jakub Matczak Jun 18 '15 at 13:27
1

"On a side note: You can only have onePrimary key in a table" what about composite primary key? – Ara Yeressian Sep 03 '18 at 13:24
1

It's still one Primary Key, @AraYeressian. – Marc.2377 Jan 17 '20 at 17:11

score 8 · Answer 2 · answered Jun 18 '15 at 09:45

8

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

answered Jun 18 '15 at 09:45

Jakub Matczak

15,341
5
46
64

score 4 · Answer 3 · answered Aug 24 '19 at 22:28

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

MySQL: is primary key unique by default?

3 Answers3

Linked