MySQL Bugs: #6062: Creating of primary key with CREATE INDEX not allowed

Bug #6062	Creating of primary key with CREATE INDEX not allowed
Submitted:	13 Oct 2004 12:01	Modified:	6 Dec 2006 17:08
Reporter:	Andrey Hristov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	4.1.4 (maybe others)	OS:	Linux (Linux 2.6.4)
Assigned to:		CPU Architecture:	Any

Description:
MySQL documenatation states :
"In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you don't assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique."
The "CREATE INDEX" statement can be used to add indexes on a table. Supported are  UNIQUE, FULLTEXT and SPATIAL ones. In MySQL unique index is quite similar to the primary key (except the topic NULL values). However an user is not allowed to create a primary key index by using the CREATE INDEX statement (see how-to-repeat). I thought that since the primary key is an index which always is named PRIMARY that creating an index with that name will succeed but in fact it did not.

Thanks

How to repeat:
mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 4.1.4-beta-debug-log |
+----------------------+
1 row in set (0.00 sec)

mysql> create table t42(a int not null);
Query OK, 0 rows affected (0.01 sec)

mysql> create unique index foobar on t42(a);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create unique index PRIMARY on t42(a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY on t42(a)' at line 1

as PRIMARY is a reserved word you have to quote it here:

  CREATE UNIQUE INDEX `PRIMARY` ON t42(i);

works fine as expected

works in 4.0 but fails on 4.1.5 -> verifed as a backwards compatibility problem

First, definitely quite a nice trick :), especially since  adding a primary key with alter table is like :
ALTER TABLE t42 ADD PRIMARY KEY(a);
but CREATE INDEX should use quoting like
CREATE UNIQUE INDEX `PRIMARY` ON t42(a);

In addition, the proposed solution does not work here :) (MySQL started without full ANSI compliance support)

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 4.1.4-beta-debug-log |
+----------------------+
1 row in set (0.00 sec)

mysql> CREATE UNIQUE INDEX `PRIMARY` ON t42(i);
ERROR 1280 (42000): Incorrect index name 'PRIMARY'
mysql> describe t42;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------------
Another box:
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.2-alpha-debug |
+-------------------+
1 row in set (0.01 sec)

mysql> create table t42(a int);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE UNIQUE INDEX `PRIMARY` ON t42(i);
ERROR 1280 (42000): Incorrect index name 'PRIMARY'
mysql> describe t42;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Thanks

This behaviour was intentionally programmed in. 
See Bug#856 
Code committed 2004-03-14

Regarding #856:
the docs say that the name is always "PRIMARY", therefore it is expected behaviour when someone creates `primary` to affect the primary key. However my idea is just to be able to create primary key with CREATE INDEX :)

CREATE INDEX `PRIMARY` ON a(b);
also did work

Andrey

Well, as it's

ALTER TABLE t42 ADD PRIMARY KEY(a);

then it would be reasonable to expect

CREATE PRIMARY INDEX ON t42(a)

to work. Though I tend to see it as a feature request, not as a bug