Description:
When using table names that contain capitalization, mysql fails to correctly manage foreign key constraints.
Error thrown is:
Cannot add or update a child row: a foreign key constraint fails (`asdf`.`actions_equations`, CONSTRAINT `actions_equations_ibfk_1` FOREIGN KEY (`equation_id`) REFERENCES `Equation` (`id`))
Have confirmed in house that this bug occurs in both 5.5.10 and 5.5.11, but not in 5.0.8.7
How to repeat:
This simple script:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
DROP TABLE IF EXISTS `action`;
CREATE TABLE `action` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `actions_equations`;
CREATE TABLE `actions_equations` (
`action_id` bigint(20) NOT NULL,
`equation_id` bigint(20) NOT NULL,
PRIMARY KEY (`action_id`,`equation_id`),
KEY `FKFF3A1DA59F5C8B19` (`action_id`),
KEY `FKFF3A1DA5C5751F99` (`equation_id`),
CONSTRAINT `actions_equations_ibfk_1` FOREIGN KEY (`equation_id`) REFERENCES `equation` (`id`),
CONSTRAINT `actions_equations_ibfk_2` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `equation`;
CREATE TABLE `equation` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
insert into action values ();
insert into equation values ();
insert into actions_equations values (1, 1);
Works, but this identical (excepting capitalization) example fails with the above error:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
DROP TABLE IF EXISTS `Action`;
CREATE TABLE `Action` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `Actions_Equations`;
CREATE TABLE `Actions_Equations` (
`action_id` bigint(20) NOT NULL,
`equation_id` bigint(20) NOT NULL,
PRIMARY KEY (`action_id`,`equation_id`),
KEY `FKFF3A1DA59F5C8B19` (`action_id`),
KEY `FKFF3A1DA5C5751F99` (`equation_id`),
CONSTRAINT `actions_equations_ibfk_1` FOREIGN KEY (`equation_id`) REFERENCES `Equation` (`id`),
CONSTRAINT `actions_equations_ibfk_2` FOREIGN KEY (`action_id`) REFERENCES `Action` (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `Equation`;
CREATE TABLE `Equation` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
insert into Action values ();
insert into Equation values ();
insert into Actions_Equations values (1, 1);
However, If you drop and recreate the foreign keys before this final insertion no error occurs:
insert into Action values ();
insert into Equation values ();
ALTER TABLE `Actions_Equations` DROP FOREIGN KEY `actions_equations_ibfk_1`;
ALTER TABLE `Actions_Equations` ADD CONSTRAINT `actions_equations_ibfk_1` FOREIGN KEY (`equation_id`) REFERENCES `Equation` (`id`);
ALTER TABLE `Actions_Equations` DROP FOREIGN KEY `actions_equations_ibfk_2`;
ALTER TABLE `Actions_Equations` ADD CONSTRAINT `actions_equations_ibfk_2` FOREIGN KEY (`action_id`) REFERENCES `Action` (`id`);
insert into Actions_Equations values (1, 1);
Suggested fix:
Capitalization of table names should not effect foreign key handling.
