MySQL 错误 1452：无法添加或更新子行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16447137/
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
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
提问by Tiny
I have created two tables in MySQL 5.6.11 as shown below by means of MySQL Workbench 5.2.47.
我通过 MySQL Workbench 5.2.47 在 MySQL 5.6.11 中创建了两个表，如下所示。
The countrytable:
该country表：
delimiter $$
CREATE TABLE `country` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INC
REMENT=2 DEFAULT CHARSET=utf8$$
The state_table:
的state_table：
delimiter $$
CREATE TABLE `state_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`state_name` varchar(45) DEFAULT NULL,
`country_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `country_fk` FOREIGN KEY (`id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT=''$$
There is one row in the countrytable with the id1. It allows only one (child) row to be inserted into its child table state_table. If more rows are attempted, then the following error occurs.
country表中有一行带有id1。它只允许将一个（子）行插入到其子表中state_table。如果尝试更多行，则会发生以下错误。
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (
social_networking.state_table, CONSTRAINTcountry_fkFOREIGN KEY (id) REFERENCEScountry(id) ON DELETE CASCADE ON UPDATE CASCADE)
错误 1452：无法添加或更新子行：外键约束失败（
social_networking.state_table, CONSTRAINTcountry_fkFOREIGN KEY (id) REFERENCEScountry(id) ON DELETE CASCADE ON UPDATE CASCADE）
SQL Statement:
SQL语句：
INSERT INTO `social_networking`.`state_table` (`id`, `state_name`, `country_id`) VALUES ('2', 'xxx', '1')
Actually, I'm trying to map these tables using an ORM (JPA) where I always see only OneToOnerelationship.
实际上，我正在尝试使用 ORM (JPA) 来映射这些表，在那里我总是只看到OneToOne关系。
What am I missing?
我错过了什么？
回答by Roman Nu?ez
Well, I find the answer, the solution, my english is not very well but I think can explain you. I get this error after try to create a trigger, My database was created in phpmyadmin, and this error was make me crazy, the problem was that I before create the trigger, I load a lot of data in my tables, and in my child table was some data that have no match in my parent table, ej: my child table "chat" have a "id_jugador=1" and in my parent table there wasn't that "id_jugador", that was my mistake, I hope help you, Argentina Rulz ;)
好吧，我找到了答案，解决方案，我的英语不是很好，但我想可以解释你。尝试创建触发器后出现此错误，我的数据库是在 phpmyadmin 中创建的，这个错误让我发疯了，问题是我在创建触发器之前，在我的表和我的孩子中加载了大量数据表是一些在我的父表中没有匹配的数据，ej：我的子表“chat”有一个“id_jugador=1”，而在我的父表中没有那个“id_jugador”，那是我的错误，希望有所帮助你，阿根廷鲁尔兹 ;)
回答by Joachim Isaksson
I think you have a typo in your foreign key constraint, country_idshould probaby be the foreign key to country. When idis the foreign key, you can only insert one row since it just happens to get id=1 which is the same id as the row in country;
我认为您的外键约束中有一个错字，country_id应该可能是country. 什么时候id是外键，你只能插入一行，因为它恰好得到 id=1 ，它与 country 中的行 id 相同；
CONSTRAINT `country_fk` FOREIGN KEY (`id`)
REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
should probably be
应该是
CONSTRAINT `country_fk` FOREIGN KEY (`country_id`)
REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
回答by Daniel Arantes Loverde
I had the same problem and it was not wrong relationships name.
I had problem with different records, ie, tried registering a record that did not exist in another table so generated this error.
Check if the record exists in another table to insert their correct relationship, otherwise, this error appears.
我有同样的问题，这不是错误的关系名称。
我遇到了不同记录的问题，即，尝试注册另一个表中不存在的记录，因此生成了此错误。
检查该记录是否存在于另一个表中以插入它们正确的关系，否则会出现此错误。
Hope this help you.
希望这对你有帮助。
回答by Tang Chanrith
example:
table1(
id1 INT PRIMARY KEY,
name1 VARCHAR(50)
)
table2(
id2,<--- want to add FOREIGN KEY to this field
name2 VARCHAR(50)
)
Before adding constraint foreign key you must have the right value between id1and id2, so you should update that id field with the value that map each other.
在添加约束外键之前，您必须在id1和之间具有正确的值id2，因此您应该使用相互映射的值更新该 id 字段。
回答by Akash Patel
Possible Solution
可能的解决方案
if you have live data, then check all column values.
如果您有实时数据，请检查所有列值。
i.e. if you have 'x'->table as primary one having 'a'->column and 'y'->table as secondary with 'b'->column, then all values in 'b'->column must exist in 'a'->column if any value that exists in 'b'->column and not exist in 'a'->column then it will give as such error..
即如果你有 'x'->table 作为主要的，有 'a'->column 和 'y'->table 作为次要的 'b'->column，那么 'b'->column 中的所有值必须存在于'a'->column 如果任何值存在于 'b'->column 而在 'a'->column 中不存在，那么它会给出这样的错误..
Hope this help.. for newbie..
希望这有助于..对于新手..
