当外键存在时，为什么“外键约束失败”？

本文介绍了当外键存在时，为什么“外键约束失败”？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一个简单的查询 UPDATE`t_timecard_detail` SET`timeoff_request_id` ='adad8e0d-c22b-41c3-a460- 6cf982729299'WHERE`id` ='cfc7a0a1-4e03-46a4-af89-069a0661cf55'; 给出这个错误 pre > 错误1452（23000）：无法添加或更新子行：外键约束失败（`demo1_timeapp`.`t_timecard_detail`，CONSTRAINT`timeoff_request_id_refs_id_48fe5c4` FOREIGN KEY（`timeoff_request_id`）REFERENCES`t_timeoff_request` （`id`）） 约束是 CONSTRAINT`timeoff_request_id_refs_id_48fe5c4` FOREIGN KEY（`timeoff_request_id`）REFERENCES`t_timeoff_request`（`id`） 虽然ID'adad8e0d-c22b-41c3-a460-6cf982729299'存在于t_timeoff_request表中 MySQL的>从t_timeoff_request选择`id`，其中`id` ='adad8e0d-c22b-41c3-a460-6cf982729299'; + -------------------------------------- + | id | + -------------------------------------- + | adad8e0d-c22b-41c3-a460-6cf982729299 | + -------------------------------------- + $ b 所以我不确定为什么mysql在外键存在的时候说外键约束失败？ mysql版本： $ mysql --version mysql Ver 14.14 Distrib 5.1。 41，debian-linux-gnu（i486）使用readline 6.1 编辑：看起来像一个表ENGINE = MyISAM和其他innodb 的输出show create table t_timecard_detail; $ $ p $ code $ CREATE TABLE t_timecard_detail（$ b $ id`varchar（36）NOT NULL，`change_time` datetime NOT NULL， （36）DEFAULT NULL，`day` date NOT NULL，`in_punch_id` varchar（36）DEFAULT NULL，`punch_in` datetime DEFAULT NULL， `pay_in` datetime DEFAULT NULL，`infraction_in_id` varchar（36）DEFAULT NULL，`schedule_in` datetime DEFAULT NULL，$ b $``type_in` varchar（10）NOT NULL， `出_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 $ b $ pay_type_id varchar（36）NOT NULL $ b $ timecard_id varchar ）NOT NULL，`comment` 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``time_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`）参考`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`）参考`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（`tim引用`t_timeoff_request`（`id`））ENGINE = InnoDB DEFAULT CHARSET = latin1 show create table t_timeoff_request; CREATE TABLE` t_timeoff_request（$ b $ 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 解决方案错误。 MySQL不应该允许您将InnoDB中的FOREIGN KEY引用添加到MyISAM表中，因为MyISAM引擎不是事务性的，InnoDb是。 尝试： ALTER TABLE t_timeoff_request ENGINE = InnoDB; I have a simple queryUPDATE `t_timecard_detail` SET `timeoff_request_id` = 'adad8e0d-c22b-41c3-a460-6cf982729299' WHERE `id` = 'cfc7a0a1-4e03-46a4-af89-069a0661cf55';which gives this errorERROR 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`))constraint is CONSTRAINT `timeoff_request_id_refs_id_48fe5c4` FOREIGN KEY (`timeoff_request_id`) REFERENCES `t_timeoff_request` (`id`)though the ID 'adad8e0d-c22b-41c3-a460-6cf982729299' exists in t_timeoff_request tablemysql> select `id` from t_timeoff_request where `id`='adad8e0d-c22b-41c3-a460-6cf982729299';+--------------------------------------+| id |+--------------------------------------+| adad8e0d-c22b-41c3-a460-6cf982729299 |+--------------------------------------+So I am not sure why mysql says 'foreign key constraint fails' when the foreign key exists?mysql version:$ mysql --versionmysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1Edit: Looks like one table has ENGINE=MyISAM and other innodboutput 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=latin1output 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 解决方案 I think it's a bug.MySQL shouldn't allow You to add FOREIGN KEY referencing to MyISAM table from InnoDB because MyISAM engine isn't transactional, InnoDb is.Try:ALTER TABLE t_timeoff_request ENGINE=InnoDB; 这篇关于当外键存在时，为什么“外键约束失败”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！