Cannot add or update a child row: a foreign key constraints. Which is not possible

Question

I'm facing a really odd problem with MySQL, I got this following error when adding a new row :

Cannot add or update a child row: a foreign key constraint fails (gestikids_demo.child_moments, CONSTRAINT fk_child_moments_moment_16 FOREIGN KEY (moment_id) REFERENCES moments (id))

But I correctly add an existing moment_id in my entry. Here's the tables defintions :

CREATE TABLE `child_moments` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `child_id` bigint(20) DEFAULT NULL,
 `moment_id` bigint(20) DEFAULT NULL,
 `day` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uq_child_moments_1` (`child_id`,`moment_id`,`day`),
 KEY `ix_child_moments_child_15` (`child_id`),
 KEY `ix_child_moments_moment_16` (`moment_id`),
 CONSTRAINT `fk_child_moments_child_15` FOREIGN KEY (`child_id`) REFERENCES `childs` (`id`),
 CONSTRAINT `fk_child_moments_moment_16` FOREIGN KEY (`moment_id`) REFERENCES `moments` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=196596 DEFAULT CHARSET=latin1

CREATE TABLE `moments` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `is_meal` tinyint(1) DEFAULT '0',
 `sort` int(11) DEFAULT NULL,
 `pole_id` bigint(20) DEFAULT NULL,
 `type_id` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `ix_moments_pole_72` (`pole_id`),
 KEY `ix_moments_type_73` (`type_id`)
) ENGINE=MyISAM AUTO_INCREMENT=117 DEFAULT CHARSET=latin1

What is wrong ?

I tested to enter manually the data, and all the fields are populated with existing IDs, but the insert fail.

Thank you for your help.

Update: If I insert a data with no moment_id, the insert is successful. I checked if the foreign key is correctly written (no mistakes in the constraints) but it doesn't seems.

Update 2:

I tried recreating a simplified version of those two tables in an other database to see if the error was really about them, and I had the following error :

Can't create table 'child_moments' (errno: 150)

The insert was the following :

CREATE TABLE `child_moments` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `moment_id` bigint(20) DEFAULT NULL,
 `day` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uq_child_moments_1` (`moment_id`,`day`),
 KEY `ix_child_moments_moment_16` (`moment_id`),
 CONSTRAINT `fk_child_moments_moment_16` FOREIGN KEY (`moment_id`) REFERENCES `moments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `moments` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `is_meal` tinyint(1) DEFAULT '0',
 `sort` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And if I remove the following lines, the insert works successfully :

 KEY `ix_child_moments_moment_16` (`moment_id`),
 CONSTRAINT `fk_child_moments_moment_16` FOREIGN KEY (`moment_id`) REFERENCES `moments` (`id`)

So the problem is clearly located at these lines, but I can't explain what and why :/

Answer 1

I finally found the problem, thanks to MySQL Creating tables with Foreign Keys giving errno: 150

It's in fact really simple : the two tables does not have the same engine ! Yes, moments is MyISAM and child_moment is InnoDB.

Switching both to InnoDB fixed the issue for me.

Hope it'll helps others too!