由于“无法添加或更新子行:外键约束失败"，MySQL .cs​​v加载失败. [英] MySQL .csv load failing due to "Cannot add or update a child row: foreign key constraint fails"

问题描述

我正在尝试将带有棒球时间表的.csv文件加载到游戏表中. csv文件内容如下:

I'm trying to load a .csv file with baseball schedules into a table of games. The csv file contents look like this:

5/17/2011,8:10 PM,14,13,Kansas City,MO
5/18/2011,8:10 PM,14,16,Chicago,IL
5/19/2011,8:10 PM,14,16,Chicago,IL
5/20/2011,7:05 PM,26,14,Columbus,OH

和我尝试插入它们的sql语句是:

and my sql statement to try and insert them is:

LOAD DATA LOCAL INFILE 'c:/ftpsite/comp.csv' INTO TABLE game
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@DATE_STR, time, awayteam_id, hometeam_id,locationcity,locationstate)
SET date = STR_TO_DATE(@DATE_STR, '%c/%e/%Y');

但是我得到了错误:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`gamelydb`.`game`, CONSTRAINT `hometeam_id_refs_id` FOREIGN KEY (`hometeam_id`) REFERENCES `team` (`id`))

哦，这是游戏桌的说明:

Oh, and here's the description of the game table:

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| date          | date        | NO   |     | NULL    |                |
| time          | time        | NO   |     | NULL    |                |
| hometeam_id   | int(11)     | NO   | MUL | NULL    |                |
| awayteam_id   | int(11)     | NO   | MUL | NULL    |                |
| locationcity  | varchar(30) | NO   |     | NULL    |                |
| locationstate | varchar(20) | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

推荐答案

您可以通过在输入前使用set foreign_key_checks = 0;禁用外键检查(请确保在运行后使用SET foreign_key_checks = 1;将其设置回原来的状态.

You can disable foreign key checks by using set foreign_key_checks = 0; before the input (make sure to set it back using SET foreign_key_checks = 1; after the run.

您真正应该做的是确保表hometeam_id和awayteam_id都指向已插入的值.如果团队表正在以与您的游戏表相同的CSV格式插入数据，请先执行团队表，但实际情况并非如此.

What you really should do is make sure that whatever table hometeam_id and awayteam_id are pointing to HAVE the values you are inserting. If the team tables are getting data inserted into them in the same CSV as your game table, do the team tables first, but that doesn't look to be the case.

最后，您可以删除hometeam_id和awayteam_id上的外键，然后像下面的示例一样在以后添加它们: ALTER TABLE table_name DROP FOREIGN KEY table_name_ibfk_1;

Finally, you can remove the foreign keys on the hometeam_id and awayteam_id and add them later like this example: ALTER TABLE table_name DROP FOREIGN KEY table_name_ibfk_1;

这篇关于由于“无法添加或更新子行:外键约束失败"，MySQL .cs​​v加载失败.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！