MySql primary key constraint with name

Question

Data definition statement:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
)

What is the value and purpose of

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)?

as opposed to this

PRIMARY KEY (P_Id)?

MySql docs do not really say much about this except for this.

Answer 1

It's the same as MySQL ignores the CONSTRAINT pk_PersonID part. You can check by creating the table and then dumping it or issuing SHOW CREATE TABLE Persons.

I guess it supports this syntax only for compatibility with other SQL servers (but ignores it for primary and other local keys) and does not store its information (the constraint name).

However for usage with foreign keys the CONSTRAINT keyword is used also in MySQL.

mysql> CREATE TABLE test.Persons (
    -> P_Id int NOT NULL,
    -> LastName varchar(255) NOT NULL,
    -> FirstName varchar(255),
    -> Address varchar(255),
    -> City varchar(255),
    -> CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
    -> );
Query OK, 0 rows affected (0.50 sec)

server$ mysqldump -p test Persons
Enter password:
--
-- Table structure for table `Persons`
--
DROP TABLE IF EXISTS `Persons`;
CREATE TABLE `Persons` (
  `P_Id` int(11) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`P_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is also test to prove MySQL doesn't store the constraint name anywhere and doesn't use it when printing errors (as mentioned for other SQL servers in question What is the purpose of constraint naming :

mysql> insert into Persons (P_Id) values(1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Persons (P_Id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'