Geek Answers Handbook

Why does “foreign key constraint fail” when a foreign key exists?

I have a simple request

UPDATE `t_timecard_detail` SET `timeoff_request_id` = 'adad8e0d-c22b-41c3-a460-6cf982729299' WHERE `id` = 'cfc7a0a1-4e03-46a4-af89-069a0661cf55';

which gives this error

 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demo1_timeapp`.`t_timecard_detail`, CONSTRAINT `timeoff_request_id_refs_id_48fe5c4` FOREIGN KEY (`timeoff_request_id`) REFERENCES `t_timeoff_request` (`id`))

limitation

 CONSTRAINT `timeoff_request_id_refs_id_48fe5c4` FOREIGN KEY (`timeoff_request_id`) REFERENCES `t_timeoff_request` (`id`)

although the identifier adad8e0d-c22b-41c3-a460-6cf982729299 'exists in the t_timeoff_request table

 mysql> select `id` from t_timeoff_request where `id`='adad8e0d-c22b-41c3-a460-6cf982729299'; +--------------------------------------+ | id | +--------------------------------------+ | adad8e0d-c22b-41c3-a460-6cf982729299 | +--------------------------------------+

So I'm not sure why mysql says that the foreign key constraint fails when the foreign key exists?

mysql version:

 $ mysql --version mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

Edit: it looks like one table has ENGINE = MyISAM and other innodb

the output of show create table t_timecard_detail;

 CREATE TABLE `t_timecard_detail` ( `id` varchar(36) NOT NULL, `change_time` datetime NOT NULL, `department_id` varchar(36) DEFAULT NULL, `day` date NOT NULL, `in_punch_id` varchar(36) DEFAULT NULL, `punch_in` datetime DEFAULT NULL, `paid_in` datetime DEFAULT NULL, `infraction_in_id` varchar(36) DEFAULT NULL, `schedule_in` datetime DEFAULT NULL, `type_in` varchar(10) NOT NULL, `out_punch_id` varchar(36) DEFAULT NULL, `punch_out` datetime DEFAULT NULL, `paid_out` datetime DEFAULT NULL, `schedule_out` datetime DEFAULT NULL, `infraction_out_id` varchar(36) DEFAULT NULL, `type_out` varchar(10) NOT NULL, `work_min` int(11) NOT NULL, `ot_min` int(11) NOT NULL, `ot2_min` int(11) NOT NULL, `pay_type_id` varchar(36) NOT NULL, `timecard_id` varchar(36) NOT NULL, `user_entered` tinyint(1) NOT NULL, `comments` varchar(256) DEFAULT NULL, `timeoff_request_id` varchar(36), PRIMARY KEY (`id`), KEY `t_timecard_detail_department_id` (`department_id`), KEY `t_timecard_detail_in_punch_id` (`in_punch_id`), KEY `t_timecard_detail_infraction_in_id` (`infraction_in_id`), KEY `t_timecard_detail_out_punch_id` (`out_punch_id`), KEY `t_timecard_detail_infraction_out_id` (`infraction_out_id`), KEY `t_timecard_detail_pay_type_id` (`pay_type_id`), KEY `t_timecard_detail_timecard_id` (`timecard_id`), KEY `t_timecard_detail_4f5ffbb5` (`timeoff_request_id`), CONSTRAINT `department_id_refs_id_1b23ee35` FOREIGN KEY (`department_id`) REFERENCES `t_department` (`id`), CONSTRAINT `infraction_in_id_refs_id_17b2e173` FOREIGN KEY (`infraction_in_id`) REFERENCES `t_pay_group_infraction` (`id`), CONSTRAINT `infraction_out_id_refs_id_17b2e173` FOREIGN KEY (`infraction_out_id`) REFERENCES `t_pay_group_infraction` (`id`), CONSTRAINT `in_punch_id_refs_id_4d13a8b2` FOREIGN KEY (`in_punch_id`) REFERENCES `t_punch` (`id`), CONSTRAINT `out_punch_id_refs_id_4d13a8b2` FOREIGN KEY (`out_punch_id`) REFERENCES `t_punch` (`id`), CONSTRAINT `pay_type_id_refs_id_70cb7404` FOREIGN KEY (`pay_type_id`) REFERENCES `t_pay_type` (`id`), CONSTRAINT `timecard_id_refs_id_7889236c` FOREIGN KEY (`timecard_id`) REFERENCES `t_timecard` (`id`), CONSTRAINT `timeoff_request_id_refs_id_48fe5c4` FOREIGN KEY (`timeoff_request_id`) REFERENCES `t_timeoff_request` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

output of show create table t_timeoff_request;

 CREATE TABLE `t_timeoff_request` ( `id` varchar(36) NOT NULL, `change_time` datetime NOT NULL, `employee_id` varchar(36) NOT NULL, `submitted_date` datetime NOT NULL, `time_off_day` date NOT NULL, `mins_charged` int(11) NOT NULL, `pay_type_id` varchar(36) NOT NULL, `employee_comment` varchar(300) NOT NULL, `approved_status` varchar(10) NOT NULL, `approved_by_id` varchar(36) DEFAULT NULL, `approved_date` datetime DEFAULT NULL, `approved_comment` varchar(300) DEFAULT NULL, PRIMARY KEY (`id`), KEY `t_timeoff_request_employee_id` (`employee_id`), KEY `t_timeoff_request_pay_type_id` (`pay_type_id`), KEY `t_timeoff_request_approved_by_id` (`approved_by_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+7
mysql foreign-keys mysql-error-1452
source share
3 answers

I think this is a mistake. MySQL should not allow you to add a FOREIGN KEY link to the MyISAM table from InnoDB, since the MyISAM mechanism is not transactional, InnoDb is.

 Try: ALTER TABLE t_timeoff_request ENGINE=InnoDB;
+18
source share

Perhaps the problem is in a different table if you set the limit to FK. Have you configured the FK parameter, for example, ON UPDATE CASCADE?

0
source share

Foreign key restrictions cannot be applied to the MyISAM engine. The table should use the innodb mechanism to support foreign key constraints.

0
source share

More articles:


All Articles