Bug #16221 multiple alter specification doesn't work in one statement,

• Submitted: 5 Jan 2006 13:10
• Modified: 5 Jan 2006 13:13
• Reporter: Gleb Paharenko
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.18
• OS: Linux (Linux)
• CPU Architecture: Any

[5 Jan 2006 13:10] Gleb Paharenko

Description:
I'm not sure about the order of alter_specifications in ALTER STATEMENT, however in my test 
case multiple alter_specifications cause an error, while separate statements work fine

How to repeat:
create table par1(a int, key (a)) engine = innodb;
create table chil1(b int, foreign key (b) references par1(`a`)) engine=innodb;
mysql> alter table chil1 drop foreign key chil1_ibfk_1, add CONSTRAINT `chil1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `par1` (`a`);
ERROR 1005 (HY000): Can't create table './test/#sql-3b86_9.frm' (errno: 121)

mysql> alter table chil1 drop foreign key chil1_ibfk_1; alter table chil1 add CONSTRAINT `chil1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `par1` (`a`);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
Fix this or document the order of execution of alter_specification

[5 Jan 2006 13:13] Heikki Tuuri

Gleb,

I think there is already a feature request for this.

The InnoDB section of the manual shows the legal ALTER syntax. You cannot have two foreign key operations in one ALTER.

Regards,

Heikki