当外键存在时，为什么“外键约束失败"? [英] Why 'foreign key constraint fails' when foreign key exists?

问题描述

我有一个简单的查询

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

这个错误

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`))

约束是

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 表中

though the ID 'adad8e0d-c22b-41c3-a460-6cf982729299' exists in t_timeoff_request table

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

所以我不确定为什么当外键存在时mysql说'外键约束失败'?

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

mysql版本:

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

看起来一个表有 ENGINE=MyISAM 和其他 innodb

Looks like one table has ENGINE=MyISAM and other innodb

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 

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

推荐答案

我认为这是一个错误.MySQL 不应允许您从 InnoDB 添加对 MyISAM 表的 FOREIGN KEY 引用，因为 MyISAM 引擎不是事务性的，而 InnoDb 是.

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;

这篇关于当外键存在时，为什么“外键约束失败"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！