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`;
