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 — Akhil
– Akhil, Commented Jun 18, 2015 at 9: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
– Roee Gavirel, Commented Jun 18, 2015 at 13:03
possible duplicate of difference between primary key and unique key — James Z
– James Z, Commented Jun 18, 2015 at 19:37

Serghei Niculaev · Accepted Answer · 2020-01-22 17:01:08Z

73

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, 2020 at 17:01

Serghei Niculaev

5157 silver badges18 bronze badges

answered Jun 18, 2015 at 9:45

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bacteria Over a year ago

another point : a table can have only one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints

Jakub Matczak Over a year ago

Actually there are more differences between unique and primary key, like primary key cannot be NULL. But that's not the question about.

Ara Yeressian Over a year ago

"On a side note: You can only have onePrimary key in a table" what about composite primary key?

Marc.2377 Over a year ago

It's still one Primary Key, @AraYeressian.

Jakub Matczak · Accepted Answer · 2015-06-18 09:45:05Z

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

Skippy le Grand Gourou · Accepted Answer · 2019-08-24 22:28:55Z

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

Collectives™ on Stack Overflow

MySQL: is primary key unique by default?

3 Answers 3

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Linked

Related