Bug #64922 Foreign Key Error on CREATE TABLE after ALTER TABLE and DROP TABLE statements.

Description:
Foreign Key Error on CREATE TABLE after ALTER TABLE and DROP TABLE statements.  The Foreign key error on CREATE TABLE appears when ALTER TABLE adds a foreign key constraint and table is renamed in one statement.

How to repeat:
CREATE DATABASE `schema1`;

USE `schema1`;

CREATE TABLE  `p1` (
  `p1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `p1_name` varchar(12) NOT NULL,
  `p1_age` tinyint(3) NOT NULL,
  PRIMARY KEY (`p1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;

CREATE TABLE  `p2` (
  `p2_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `p2_name` varchar(12) NOT NULL,
  `p2_age` tinyint(3) NOT NULL,
  PRIMARY KEY (`p2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;

CREATE TABLE  `c1` (
  `c1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `p1_id_fk` int(10) unsigned NOT NULL,
  `p2_id_fk` int(10) unsigned NOT NULL,
  `c1_name` varchar(12) NOT NULL,
  PRIMARY KEY (`c1_id`),
  CONSTRAINT `c1_f1` FOREIGN KEY (`p1_id_fk`) REFERENCES `p1` (`p1_id`),
  CONSTRAINT `c1_f2` FOREIGN KEY (`p2_id_fk`) REFERENCES `p2` (`p2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;

ALTER TABLE `c1` DROP FOREIGN KEY `c1_f2`;

ALTER TABLE `c1` ADD CONSTRAINT `c1_f2` FOREIGN KEY (`p2_id_fk` ) REFERENCES `p2` (`p2_id` ), RENAME TO `c1_new`;

DROP TABLE `c1_new`;

CREATE TABLE  `c1` (
  `c1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `p1_id_fk` int(10) unsigned NOT NULL,
  `p2_id_fk` int(10) unsigned NOT NULL,
  `c1_name` varchar(12) NOT NULL,
  PRIMARY KEY (`c1_id`),
  CONSTRAINT `c1_f1` FOREIGN KEY (`p1_id_fk`) REFERENCES `p1` (`p1_id`),
  CONSTRAINT `c1_f2` FOREIGN KEY (`p2_id_fk`) REFERENCES `p2` (`p2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;

ERROR 1005 (HY000): Can't create table 'schema1.c1' (errno: 121)
mysql> show engine innodb status;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
120410  3:44:12 Error in foreign key constraint creation for table `schema1`.`c1`.
A foreign key constraint of name `schema1`.`c1_f1`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Suggested fix:
The workaround is to separate statements for renaming the table and adding a foreign key:

ALTER TABLE `c1` ADD CONSTRAINT `c1_f2` FOREIGN KEY (`p2_id_fk` ) REFERENCES `p2` (`p2_id` );
ALTER TABLE `c1` RENAME TO `c1_new`;

Thank you for the bug report. Verified with 5.1.61 also.

This appears to be a duplicate of #58215
http://bugs.mysql.com/bug.php?id=58215

Duplicate of

Bug#48070 Foreign keys may disappear after ALTER TABLE