SQL #1452 - 无法添加或更新子行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16594672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
#1452 - Cannot add or update a child row: a foreign key constraint fails
提问by Glenda
When I wan't to connect two tables with eachother then I get the message : #1452 - Cannot add or update a child row: a foreign key constraint fails....I want to connect oauth_uid2 (primary key) from the facebook_users tabel to the bugs tabel with the foreign key oauth_uid2. But I get always this message. I have already cleared my data but nothing works.Also in the table users_facebook I have 1 record.
当我不想将两个表相互连接时，我收到消息：#1452 - 无法添加或更新子行：外键约束失败......我想从 facebook_users 表中连接 oauth_uid2（主键）到带有外键 oauth_uid2 的错误表。但我总是收到这条消息。我已经清除了我的数据，但没有任何效果。也在 users_facebook 表中我有 1 条记录。
1452 - Cannot add or update a child row: a foreign key constraint fails (
phples.bugs, CONSTRAINTbugs_ibfk_1FOREIGN KEY (oauth_uid2) REFERENCESusers_facebook(oauth_uid2) ON DELETE CASCADE ON UPDATE CASCADE)
1452 - 无法添加或更新子行：外键约束失败 (
phples.bugs, CONSTRAINTbugs_ibfk_1FOREIGN KEY (oauth_uid2) REFERENCESusers_facebook(oauth_uid2) ON DELETE CASCADE ON UPDATE CASCADE)
bugs table: FK = oauth_uid2, PK= bug_id
错误表：FK = oauth_uid2，PK= bug_id
#Name Type Collation Attributes Null Default Extra Action
1 bug_id int(30) No None AUTO_INCREMENT Change Drop Browse distinct values Primary Unique Index Spatial Fulltext
2 bugtitle varchar(50) utf8_unicode_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext
3 bugdescription varchar(500) utf8_unicode_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext
4 oauth_uid2 int(30) No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext
users_facebook table: PK= oauth_uid2
users_facebook 表：PK= oauth_uid2
# Name Type Collation Attributes Null Default Extra Action
1 oauth_uid2 int(30) No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext
2 email varchar(70) utf8_unicode_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext
回答by Marc B
You've already got the tables linked, which is where the error is coming from. You need to make sure that you have a record in users_facebookBEFOREyou try to insert a record into bugswith the same oauth_uid2, e.g.
您已经链接了表，这就是错误的来源。在尝试使用相同的 oauth_uid2插入记录users_facebook之前，您需要确保您有记录bugs，例如
users_facebook has records with oauth_uid2 `10`, `20`, `30`
you try to insert a record into bugswith
您尝试插入一条记录bugs与
INSERT INTO bugs (oauth_uid2) VALUES (10) // works, there's a matching record in users_facebook
INSERT INTO bugs (oauth_uid2) VALUES (15) // fails, there's no user with that id.
回答by user3441015
If you backup your Magento database using other tools, like phpMyAdmin or Navicat, these special statements will be missing. When you attempt to run the .sql file, you will get errors like these:
如果您使用其他工具（如 phpMyAdmin 或 Navicat）备份您的 Magento 数据库，这些特殊语句将丢失。当您尝试运行 .sql 文件时，您将收到如下错误：
Cannot add or update a child row: a foreign key constraint fails
无法添加或更新子行：外键约束失败
This error occurs because the data you are importing is provided table by table, row by row, without regard to the logical structure and integrity of the database.
出现此错误是因为您导入的数据是逐表、逐行提供的，不考虑数据库的逻辑结构和完整性。
To restore a .sql file backup without constraint checking, simply add the following statements at the beginning of your .sql file:
要在没有约束检查的情况下恢复 .sql 文件备份，只需在 .sql 文件的开头添加以下语句：
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;
At the end of the file, add the statements required to turn on constraint checking again:
在文件末尾，添加再次打开约束检查所需的语句：
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;
you will be able to do this.
你将能够做到这一点。
