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 — jpw
– jpw, Commented Jul 30, 2015 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
– jpw, Commented Jul 30, 2015 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
– Marki555, Commented Jul 30, 2015 at 22:25

Community · Accepted Answer · 2017-05-23 11:43:52Z

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'

Collectives™ on Stack Overflow

MySql primary key constraint with name

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related