MySQL Bugs: #15045: can't drop and recreate foreign key in a single ALTER TABLE statement

Bug #15045	can't drop and recreate foreign key in a single ALTER TABLE statement
Submitted:	18 Nov 2005 9:35	Modified:	13 May 2010 16:04
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1	OS:	Windows (windows xp)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
ALTER TABLE allows to combine several changes into one ALTER statement,
this is often needed for performance reasons as each ALTER TABLE creates
a new copy of the source table.

Dropping and redefining a regular key in one statement is possible:

 alter table a drop index IDX_1, add index IDX_1 (b); 

but dropping and redefining a foreign key fails: 

 alter table a drop foreign key fk_b, add constraint fk_b foreign key (b) references b(d);

with error 121: Duplicate key on write or update

and an error message like this in the error log:

  Error in foreign key constraint creation for table `test/#sql-824_a`.
  A foreign key constraint of name `test/fk_b` already exists.

How to repeat:

 -- create tables
 drop table if exists a;
 create table a (a int, b int)  engine=innodb;
 drop table if exists b;
 create table b (c int, d int)  engine=innodb;

 -- simple key test works ok
 alter table a add index IDX_1 (a);
 alter table a drop index IDX_1, add index IDX_1 (b); 

 -- foreign key test fails with error 121
 alter table b add index IDX_C (c);
 alter table b add index IDX_D (d);

 alter table a add constraint fk_b foreign key (b) references b(c);
 alter table a drop foreign key fk_b, add constraint fk_b foreign key (b) references b(d);

Hi!

I think this has been reported also earlier in the bugs database.

The ALTER syntax that InnoDB supports is documented in the manual. No simultaneous drop of a foreign key and create one is supported.

I am changing this to a 'Feature request'.

Regards,

Heikki

I can't find any hint in the manual that this kind of operation is 
not allowed with foreign keys, if this really intended behavior then 
it should at least be documented on

http://dev.mysql.com/doc/refman/4.1/en/alter-table-problems.html

and 

http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

Paul,

please document this restriction in ALTER TABLE.

Regards,

Heikki

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Documentation was updated to reflect the current behavior,
changed to "feature request" now ...

Applicable to 5.7.7-rc

mysql-5.7.7-rc-debug> drop table if exists a;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql-5.7.7-rc-debug>  create table a (a int, b int)  engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql-5.7.7-rc-debug>  drop table if exists b;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql-5.7.7-rc-debug>  create table b (c int, d int)  engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql-5.7.7-rc-debug> alter table a add index IDX_1 (a);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-5.7.7-rc-debug>  alter table a drop index IDX_1, add index IDX_1 (b); 
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-5.7.7-rc-debug> alter table b add index IDX_C (c);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-5.7.7-rc-debug>  alter table b add index IDX_D (d);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-5.7.7-rc-debug> alter table a add constraint fk_b foreign key (b) references b(c);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql-5.7.7-rc-debug>  alter table a drop foreign key fk_b, add constraint fk_b foreign key (b) references b(d);
ERROR 1022 (23000): Can't write; duplicate key in table '#sql-4f1b_3'

C'mon guys, it's been 12 and a half years :-(

I'm trying to write some tooling for MySQL and one of the features is being able to update foreign keys. It just fails if I try to drop and recreate a foreign key in a single ALTER statement.