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

[Jaime Sicam]

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

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(localhost)> show tables;
+-------------------+
| Tables_in_schema1 |
+-------------------+
| p1 |
| p2 |
+-------------------+
2 rows in set (0.00 sec)

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_NAME='c1';
Empty set (0.00 sec)

mysql(localhost)> show engine innodb status\G
....
....
------------------------
LATEST FOREIGN KEY ERROR
------------------------
120409 15:54:34 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.

[Laurynas Biveinis (laurynas-biveinis)]

The upstream bug http://bugs.mysql.com/bug.php?id=64922 is commented (but not closed) as a duplicate of http://bugs.mysql.com/bug.php?id=58215, which is closed as fix in 5.6.6. Thus, it needs re-verification for 5.6 and ideally all the versions.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1907