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.

possible duplicate of [What is the purpose of constraint naming](http://stackoverflow.com/questions/1397440/what-is-the-purpose-of-constraint-naming) — jpw, Jul 30 '15 at 22:11
The purpose is to be able to refer to, and have the system refer to, the constraint by a known name, instead of some automatically generated. It makes things easier. It's all covered in answers to the duplicate question. — jpw, Jul 30 '15 at 22:14
@jpw that Q/A is for SQL in general. This Q is about MySQL and my tests show that MySQL ignores the constraint name. — Marki555, Jul 30 '15 at 22:25
@Marki555 Good catch, I wasn't aware that MySQL ignored it (I'm more familiar with MS SQL), I'll withdraw the duplicate flag as it doesn't fully apply to MySQL although it's correct in general. — jpw, Jul 30 '15 at 22:29
seems like MySql ignores a couple of things. Including the `check` constraint. — Robert, Jul 30 '15 at 22:31

score 9 · Accepted Answer · edited May 23 '17 at 11:43

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'

MySql primary key constraint with name

1 Answers1

Linked

Related