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

[8 Feb 2005 22:28] Stefano Tonello
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
[14 Feb 2005 14:20] Michael G. Zinner
Thanks for reporting. Could you state which server version you are using?
[14 Feb 2005 15:31] Stefano Tonello
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)
[23 May 2005 15:43] Marko Mäkelä
You can argue that this isn't a bug in the Query Browser, but in InnoDB. See Bug #8919.
[21 Nov 2006 7:49] Mike Lischke
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.