MySQL Bugs: #62045: The reserved word PRIMARY behaves weird

Bug #62045	The reserved word PRIMARY behaves weird
Submitted:	1 Aug 2011 10:48	Modified:	15 Aug 2011 14:10
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
Not an important issue.  Just bumped into it by accident.

The reserved word PRIMARY cannot be used as an identifer (also not if `backticked` properly) for an index unless the index is a PRIMARY KEY. Other reserved words can be used. The reserved words PRIMARY can be used for naming a column though.

How to repeat:
DROP TABLE IF EXISTS `blah`;
CREATE TABLE `blah` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `txt` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `primary` (`txt`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- Error Code: 1280
-- Incorrect index name 'primary'

DROP TABLE IF EXISTS `blih`;
CREATE TABLE `blih` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `txt` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `range` (`txt`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- success

DROP TABLE IF EXISTS `bluh`;
CREATE TABLE `bluh` (
  `primary` INT(11) NOT NULL AUTO_INCREMENT,
  `txt` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`primary`),
  KEY `range` (`txt`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- success

Suggested fix:
It seem incorrect logic to me to perform check against the name/identifier as such. I am not creating a Primary Key but an 'ordinary' Key named `primary`. 

I also find this documented nowhere.

OK .. explained: 

SHOW INDEX FROM blih;

The Primary Key is actually *named* PRIMARY in MySQL.  So no matter if I like it or not, I will have to accept it.

So changing this to 'documentation' category. There is a small speciality with PRIMARY keyword that should have a note (probably here: http://dev.mysql.com/doc/refman/5.1/en/identifiers.html in addition to "If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it") that `primary` cannot be used as an identifer for an index. But anyway most people will not read it anyway, so ... 

(but I actually did plan to have two plain indexes on a table named 'primary' and 'secondary'!)

Thank you for the documentation request.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added to CREATE TABLE section:

"
The name of a PRIMARY KEY is always PRIMARY, which thus cannot beused as the name for any other kind of index. 
"

Added to DROP INDEX section:

"
To drop a primary key, the index name is always PRIMARY, which mustbe specified as a quoted identifier because PRIMARY is a reserved
word: 

DROP INDEX `PRIMARY` ON t;
"

Added to SHOW INDEX section:

"
If the index is the primary key, the name is always PRIMARY. 
"