MySQL Bugs: #54134: changes to tables and columns where not cascaded to referencing keys

Bug #54134	changes to tables and columns where not cascaded to referencing keys
Submitted:	1 Jun 2010 10:55	Modified:	17 Jun 2010 11:39
Reporter:	Michael Schramm	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S4 (Feature request)
Version:	5.2.21 RC	OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	changes, foreign keys

Description:
if you ceate a foreign key that references another table.column
and have to change the referenced table.column later on. (change of tablename or column name) the foreign key doesn't notice that changes and is not working anymore.

How to repeat:
like describes above in the first part

Suggested fix:
Please trigger inside the workbench changes to objects that references on changes data.

Many thanks for writing a bug report.

Here is the how to reproduce:

DROP SCHEMA IF EXISTS bug54134;
CREATE SCHEMA bug54134;
USE bug54134;
CREATE TABLE t(i INTEGER, j INTEGER, k INTEGER, PRIMARY KEY(i)) ENGINE=INNODB;
CREATE TABLE t1(i INTEGER, j INTEGER, k INTEGER, PRIMARY KEY(i), FOREIGN KEY(j) REFERENCES t(i)) ENGINE=INNODB;

Open Workbench
Create EER model from existing database
double click table t for editing
change column i into column bla
Sync model

ALTER TABLE `dbname`.`t` CHANGE COLUMN `i` `bla` INT(11) NOT NULL DEFAULT '0'  
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`bla`);

Unfortunately, the foreign key from t1 is not altered.

The statement will fail with error 150 (Foreign key constraint is incorrect formed)

Correct would be:
ALTER TABLE dbname.fk_tab DROP FOREIGN KEY fk_name;
ALTER TABLE `dbname`.`t` CHANGE COLUMN `i` `bla` INT(11) NOT NULL DEFAULT '0'  
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`bla`);
ALTER TABLE dbname.fk_tab add FOREIGN KEY fk_name (col) REFERENCES t(bla);

fix confirmed in repository

An entry has been added to the 5.2.23 changelog:

After a foreign key was created, and the referenced table or column changed, the change was not reflected in the foreign key, resulting in errors.