MySQL Bugs: #1956: Parser allows naming of PRIMARY KEY

Bug #1956	Parser allows naming of PRIMARY KEY
Submitted:	25 Nov 2003 16:23	Modified:	27 Sep 2008 9:31
Reporter:	Arjen Lentz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	4.0.12, 4.1.0, 5.1.52	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any

Description:
When specifying a PRIMARY KEY, the parser still accepts a name for the index even though the MySQL server will always name it PRIMARY.
So, the parser should NOT accept this, otherwise a user can get into a confusing situation as his input is ignored/changed.

How to repeat:
CREATE TABLE pk (i INT NOT NULL);
ALTER TABLE ADD PRIMARY KEY bla (i);

'bla' after PRIMARY KEY should not be accepted.

Suggested fix:
Fix grammar in parser.

>'bla' after PRIMARY KEY should not be accepted.

Or, rather, only 'PRIMARY' should be. This sould help dumps, shouldn't it ?

Reciprocally, no other index should be named as 'PRIMARY'.

Verified against 5.1.29.
There is no reason to accept broken syntax.
Should be checked with the standard, and either documented or fixed.

It's an annoying bug when doing metadata reports and it is still not fixed in 5.1.39.
Andynic

Still happens in 5.1.52:

macbook-pro:5.1 openxs$ bin/mysql -uroot test2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE pk (i INT NOT NULL);
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE pk ADD PRIMARY KEY bla (i);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pk\G
*************************** 1. row ***************************
       Table: pk
Create Table: CREATE TABLE `pk` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Still true in 8.0.3, and I made this test just to show that it breaks any rule of common sense:

mysql> use test
Database changed
mysql> CREATE TABLE `pk` (
    ->   `i` int(11) NOT NULL,
    ->   PRIMARY KEY (`i`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table pk drop index i;
ERROR 1091 (42000): Can't DROP 'i'; check that column/key exists
mysql> alter table pk drop index `PRIMARY`;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

Also, Bug #92242

Still true in 8.0.20 INNODB engine 
even when specifying with syntax :
CREATE TABLE TEST (
COL_PK BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
CONSTRAINT PK_TEST PRIMARY KEY (COL_PK)
);

with above syntax, Create table is OK but primary key constraint name is not taken into account.