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.
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.
Primary key is always unique by definition. Not only in MySQL. So you don't need any additional unique key.
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 |
+--------+---------------------------…+
