当外键存在时，为什么“外键约束失败”？ [英] 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'; 
  

给出这个错误

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


约束是

< pre $ 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 query

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

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 table

mysql> 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 --version
mysql  Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

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

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

解决方案

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屋！