Bug #23585 Foreign Key constraint prevents inserting child, but parent row exists.
Submitted: 24 Oct 2006 15:54 Modified: 24 Oct 2006 17:10
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22-Debian_0ubuntu6.06.2-log OS:Linux (Ubuntu)
Assigned to: CPU Architecture:Any

[24 Oct 2006 15:54] Baron Schwartz
When trying to insert a row, a FK constraint says no parent row exists.  The parent row does exist.

Exact error message:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/reporttemplatesubreport`, CONSTRAINT `reporttemplatesubreport_ibfk_2` FOREIGN KEY (`subreport`) REFERENCES `subreport` (`subreport`))

There may be something magical about the string 'PlatformsByWeekmargnonbrand' because we can insert other strings okay.

The contents of the two parent tables after the successful inserts:

| subreport                   | module                        |
| PlatformsByWeekmargnonbrand | PlatformsByWeek_marg_nonbrand |
1 row in set (0.00 sec)

| reporttemplate | name                |
|              6 | nonbrandstandardDPS |
1 row in set (0.00 sec)

(this bug also repeatable on 5.0.21-log on Gentoo)

How to repeat:
drop table if exists reporttemplatesubreport;
drop table if exists subreport;
drop table if exists reporttemplate;

CREATE TABLE `subreport` (
  `subreport` varchar(50) NOT NULL,
  `module` varchar(60) NOT NULL,
  PRIMARY KEY  (`subreport`)

CREATE TABLE `reporttemplate` (
  `reporttemplate` int(11) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY  (`reporttemplate`)

CREATE TABLE `reporttemplatesubreport` (
  `reporttemplate` int(11) NOT NULL,
  `tab` smallint(6) NOT NULL,
  `subreport` varchar(25) NOT NULL,
  PRIMARY KEY  (`reporttemplate`,`tab`),
  KEY `subreport` (`subreport`),
  CONSTRAINT `reporttemplatesubreport_ibfk_1` FOREIGN KEY (`reporttemplate`) REFERENCES `reporttemplate` (`reporttemplate`),
  CONSTRAINT `reporttemplatesubreport_ibfk_2` FOREIGN KEY (`subreport`) REFERENCES `subreport` (`subreport`)

insert into subreport(subreport, module)
  values ('PlatformsByWeekmargnonbrand', 'PlatformsByWeek_marg_nonbrand');

insert into reporttemplate(reporttemplate, name)
  values (6, 'nonbrandstandardDPS');

select * from subreport;

select * from reporttemplate;

insert into reporttemplatesubreport(reporttemplate, tab, subreport)
  values (6, 25, 'PlatformsByWeekmargnonbrand');
[24 Oct 2006 17:10] Baron Schwartz
It's user error.  My coworker just discovered that the column's character length limit is truncating the value.  Sorry for the false bug report.

I should have let him report it so this wouldn't go on my record ;-)