MySQL Composite Foreign Key Insert Failing

Question

I have 2 tables with a composite foreign key between the 2. When I try to insert a row into the child table, I get a restraint failure, even though the values exist in the parent table.

Here's a overview of the parent table:

CREATE TABLE `residual_reports` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `processor` enum('1','2','3') NOT NULL,
  `posting_date` date NOT NULL DEFAULT '0000-00-00',
  `approved_on` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `posting_date_2` (`processor`,`posting_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The child table has the foreign key to the processor and posting date columns:

CREATE TABLE `residual_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mid` varchar(29) DEFAULT NULL,
  `processor` enum('1','2','3') NOT NULL,
  `posting_date` date NOT NULL,
  ......
  PRIMARY KEY (`id`),
  KEY `residual_data_ibfk_1` (`processor`,`posting_date`),
  CONSTRAINT `residual_data_ibfk_1` FOREIGN KEY (`processor`, `posting_date`) REFERENCES `residual_reports` (`processor`, `posting_date`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I inserted a row into the residual_reports table with processor = 1, and posting_date = 2010-03-10.

When I try to insert into the residual_data table with processor = 1, and posting_date = 2010-03-10.

INSERT INTO `residual_data`(processor,posting_date) VALUES ('1','2010-03-10');

I get an:

[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (residual_data, CONSTRAINT residual_data_ibfk_1 FOREIGN KEY (processor, posting_date) REFERENCES residual_reports (processor, posting_date) ON UPDATE CASCADE)

Verified that the values definitely exist in the parent table, but still get foreign key restraint errors. Is there something I'm missing with a composite foreign key?

Answer 1

Your code works for me as-is. Is it possible that changes to residual_reports have not been committed yet?

Answer 2

I would suspect the ENUM's, did you do something with them afterwards? Did you change values or so?

Answer 3

What Mysql version do you use? It seems that they have similar problems before with enum values http://bugs.mysql.com/bug.php?id=24985