Bug #36548 If primary key column renamed, foreign key references must use original name
Submitted: 6 May 2008 21:40 Modified: 7 May 2008 9:15
Reporter: Anonymous Anonymous Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.23-rc OS:MacOS (Leopard)
Assigned to: Assigned Account CPU Architecture:Any
Tags: foreign key, rename

[6 May 2008 21:40] Anonymous Anonymous
Description:
If I create an innodb table and then rename the primary key column, and then try to create a second table with a foreign key to the first table, I am unable to reference it using the new column name. I must use the original column name to make it work.

How to repeat:
-- create an innodb table with a primary key column
mysql> create table A (id_one int unsigned primary key auto_increment) engine=innodb;
Query OK, 0 rows affected (0.46 sec)

-- rename the primary key column
mysql> alter table A change id_one id_two int unsigned auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- the following should work because we're using the new name for the primary key colum, but it fails
mysql> create table B (id_two int unsigned, foreign key (id_two) references A(id_two)) engine=innodb;
ERROR 1005 (HY000): Can't create table 'test.b' (errno: 150)

-- this works but really it should fail because column id_one was renamed to id_two
mysql> create table B (id_two int unsigned, foreign key (id_two) references A(id_one)) engine=innodb;
Query OK, 0 rows affected (0.13 sec)

Suggested fix:
All foreign key references should use current names. Attempts to use old names should fail.
[6 May 2008 22:12] Anonymous Anonymous
I just downloaded and installed the latest version at the time of this writing (5.1.24-rc) and confirmed that this bug still exists in the latest version.
[6 May 2008 22:20] MySQL Verification Team
Looks like duplicate of bug: http://bugs.mysql.com/bug.php?id=21704.
[6 May 2008 22:42] Anonymous Anonymous
But according to http://bugs.mysql.com/bug.php?id=21704 :

---- snip ----

[15 Feb 12:25] Alexander Nozdrin
Just added a test case. The bugs is not repeated in 5.1.24-rc

---- end snip ----

Yet, I confirmed that the problem I've identified above still exists in 5.1.24-rc. Are you certain it's the same issue?
[6 May 2008 22:55] MySQL Verification Team
I will test against the latest source, so if your case is repeatable so still
there is an issue here.
[7 May 2008 9:15] Heikki Tuuri
This problem of foreign keys under a column rename is a known problem.

MySQL's upcoming SQL layer foreign keys may fix the problem in the coming years.