MySQL Bugs: #20525: Can't drop primary key if there's a foreign key

Bug #20525	Can't drop primary key if there's a foreign key
Submitted:	18 Jun 2006 20:43	Modified:	13 May 2010 16:12
Reporter:	Peter Gulutzan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.23-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If a table t has a foreign key, I can't say
ALTER TABLE t DROP PRIMARY KEY
 

How to repeat:
mysql> create table t1 (s1 int, primary key (s1)) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2 (s1 int, primary key (s1), foreign key (s1) references t1 (s1)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t2 drop primary key;
ERROR 1025 (HY000): Error on rename of './db9/#sql-4099_1' to './db9/t2' (errno: 150)

Dropping the primary key leads to a violation of the "In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order" condition, but unlike on a CREATE TABLE the "Such an index is created on the referencing table automatically if it does not exist" behavior is not triggered (quoted from http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html )

Not sure whether this is really a but, i.e. whether DROP KEY should trigger the creation of an implicit key here ... setting this to "verified" for now, to have higher forces than me decide on this

Konstantin's foreign key implementation may solve this in the future.