MYSQL:无法添加或更新子行:外键约束失败
我收到错误:
无法添加或更新子行:外键约束失败 (mydb/requests
, CONSTRAINT requests_ibfk_5
FOREIGN KEY (fixture_id
) >) REFERENCES fixtures
(fix_id
) ON UPDATE CASCADE ON DELETE CASCADE)
我有以下表结构:
CREATE TABLE IF NOT EXISTS `requests` (
`request_id` int(11) unsigned NOT NULL auto_increment,
`fixture_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`date_added` datetime NOT NULL,
`date_modified` datetime default NULL,
PRIMARY KEY (`request_id`),
UNIQUE KEY `fixture_id_2` (`fixture_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `date_added` (`date_added`),
KEY `fixture_id` (`fixture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17 ;
CREATE TABLE IF NOT EXISTS `fixtures` (
`id` int(11) unsigned NOT NULL auto_increment,
`fix_id` int(11) unsigned NOT NULL default '0',
`fixture_date` date default NULL,
`kickoff` time default NULL,
`venue` varchar(35) default NULL,
`home_score` tinyint(4) default NULL,
`away_score` tinyint(4) default NULL,
`date_added` datetime default NULL,
`date_modified` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `fix_id` (`fix_id`),
KEY `fixture_date` (`fixture_date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=383 ;
ALTER TABLE `requests`
ADD CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
ADD CONSTRAINT `requests_ibfk_5` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`fix_id`) ON DELETE CASCADE ON UPDATE CASCADE;
如果我更新父表(fixtures)的 fix_id 字段上的记录,在子表(请求)中有一个共享 id (fixture_id) 我收到上述错误。
我不明白为什么这个完整性约束会失败。两个表都已经具有应级联的正确数据?
非常感谢任何帮助。
I am getting the error:
Cannot add or update a child row: a foreign key constraint fails (mydb/requests
, CONSTRAINT requests_ibfk_5
FOREIGN KEY (fixture_id
) REFERENCES fixtures
(fix_id
) ON UPDATE CASCADE ON DELETE CASCADE)
I have the following table structure:
CREATE TABLE IF NOT EXISTS `requests` (
`request_id` int(11) unsigned NOT NULL auto_increment,
`fixture_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`date_added` datetime NOT NULL,
`date_modified` datetime default NULL,
PRIMARY KEY (`request_id`),
UNIQUE KEY `fixture_id_2` (`fixture_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `date_added` (`date_added`),
KEY `fixture_id` (`fixture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17 ;
CREATE TABLE IF NOT EXISTS `fixtures` (
`id` int(11) unsigned NOT NULL auto_increment,
`fix_id` int(11) unsigned NOT NULL default '0',
`fixture_date` date default NULL,
`kickoff` time default NULL,
`venue` varchar(35) default NULL,
`home_score` tinyint(4) default NULL,
`away_score` tinyint(4) default NULL,
`date_added` datetime default NULL,
`date_modified` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `fix_id` (`fix_id`),
KEY `fixture_date` (`fixture_date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=383 ;
ALTER TABLE `requests`
ADD CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
ADD CONSTRAINT `requests_ibfk_5` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`fix_id`) ON DELETE CASCADE ON UPDATE CASCADE;
If I update a record on the fix_id field the parent table (fixtures), that has a shared id (fixture_id) in the child table (requests) I get the above error.
I cannot see why this integrity constraint is failing. Both tables already have the correct data it should cascade through?
Any help greatly appreciated.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这都是我自己的错误。现实中我在同一领域有两个外国的限制。我只需要脱掉一件。
This was all my own error. I had two foreign constraints on the same field in reality. I just needed to take one off.
此错误是由于引用其他表而发生的,两个表中应该存在相同的 id/数据。如果没有,请使用 where 条件删除不存在的数据。
例子
update tablename a set = 'field' (从其他表 b 中选择字段) where a.data = b.data;
This error occurs due to the reference of other table, in both tables same id's/data should exist. If not please use where conditions to remove not existing data.
example
update tablename a set = 'field' (select field from othertable b) where a.data = b.data;