Bug #60909 Foreign Key Constraint failure on capitalized table names
Submitted: 18 Apr 2011 20:34 Modified: 19 Apr 2011 13:08
Reporter: Ian Bentley Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.10 OS:MacOS (10.6)
Assigned to: CPU Architecture:Any
Tags: foreign key failure capitalization

[18 Apr 2011 20:34] Ian Bentley
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.
[19 Apr 2011 3:14] Valeriy Kravchuk
Looks like a duplicate of bug #60196 and/or bug #60309. Please, check.
[19 Apr 2011 12:48] Ian Bentley
I can confirm that this is a duplicate.
[19 Apr 2011 13:08] Valeriy Kravchuk
Duplicate of bug #60196.