MySQL Bugs: #31572: Simplify renaming a field when it has a foreign key

Bug #31572	Simplify renaming a field when it has a foreign key
Submitted:	12 Oct 2007 18:26	Modified:	13 May 2010 16:03
Reporter:	Raymond DeRoo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.48	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When renaming a column which has a foreign key reference the rename fails. Oracle and PostgreSQL both support the rename and handle the change to the foreign without any action on the users part. MySQL requires the Fk to be dropped, the name changed, and the FK to be recreated.

How to repeat:
CREATE TABLE reftable1 (
   ref1 INTEGER,
   CONSTRAINT pk_reftable1 PRIMARY KEY( ref1 )
) ENGINE = InnoDB;

CREATE TABLE testtable (
   key1 INTEGER,
   key2 INTEGER,
CONSTRAINT pk_test PRIMARY KEY( key1, key2 )
) ENGINE = InnoDB;

ALTER TABLE testtable 
ADD CONSTRAINT fk_ref1 FOREIGN KEY ( key1 ) REFERENCES reftable1 ( ref1 )
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE testtable CHANGE key1 nkey1 INTEGER;

Suggested fix:
Instead of throwing an error, handle the foreign change seamlessly for the user.

Was this bug / feature request fixed?
I can't reproduce it on 6.0.