MySQL Bugs: #8377: Can't change column name associeted with foreign keys

Bug #8377	Can't change column name associeted with foreign keys
Submitted:	8 Feb 2005 23:28	Modified:	21 Nov 2006 8:49
Reporter:	Stefano Tonello
Status:	Not a Bug
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.5	OS:	Microsoft Windows (Windows XP)
Assigned to:	Michael G. Zinner	Target Version:
Tags:	Object Editors

Description:
When you change a column name that is associated to a foreign key with query browser's
table editor, you recieve an error:
MySQL Error number 1005, can't create table (errno 121)

How to repeat:

Here a little sample.
I have a table, called "usergroups" that links two tables (namely "user" and "groups").
It contains only to fields, "user" and "groups" that links to the two tables with two
foreing keys.
Now I wish to change field name form "groups" to "groupID". Than i change it in column
name column of listview control. This automaticaly change name of Primary Index Column
(in index settings), but it doesn't in associated index or in associated foreign key. I
change it manualy an then apply changes.

For SQL command:
ALTER TABLE `cantilever`.`usergroups` CHANGE COLUMN `Group` `GroupID` INTEGER UNSIGNED
NOT NULL DEFAULT 0,
 DROP INDEX `fk_group`,
 ADD INDEX `fk_group` USING BTREE(`GroupID`),
 DROP FOREIGN KEY `fk_group`,
 ADD CONSTRAINT `fk_group` FOREIGN KEY `fk_group` (`GroupID`)
    REFERENCES `groups` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
I give the 1005 error.

Suggested fix:
You have to split automaticaly generated SQL Command (first DROP and than Change Column
Name):

first:
ALTER TABLE `cantilever`.`usergroups`
 DROP INDEX `fk_group`,
 DROP FOREIGN KEY `fk_group'

second:
ALTER TABLE `cantilever`.`usergroups` CHANGE COLUMN `Group` `GroupID` INTEGER UNSIGNED
NOT NULL DEFAULT 0,
 ADD INDEX `fk_group` USING BTREE(`GroupID`),
 ADD CONSTRAINT `fk_group` FOREIGN KEY `fk_group` (`GroupID`)
    REFERENCES `groups` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

so it's work fine

Thanks for reporting. Could you state which server version you are using?

This is my server version:

C:\Programmi\MySQL\MySQL Server 4.1\bin>mysql.exe --version
mysql.exe  Ver 14.7 Distrib 4.1.8, for Win95/Win98 (i32)

You can argue that this isn't a bug in the Query Browser, but in InnoDB. See Bug #8919.

QB is using valid SQL commands to manipulate objects. It would be out of its scope to deal
with specialities of each storage engine, particularly, when you consider the ability to
plug in custom engines.