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
Triage: Triaged: D5 (Feature request)

[12 Oct 2007 18:26] Raymond DeRoo
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.
[8 Oct 1:18] Federico Razzoli
Was this bug / feature request fixed?
I can't reproduce it on 6.0.