找出哪一行在MySQL中添加约束失败 [英] Find out which row fails on adding a constraint in MySQL

问题描述

对于此错误消息，mysqlimport是否有办法输出未通过约束的特定行?这对调试很有用.否则，我不知道哪一行有此问题.

For this error message, is there a way for mysqlimport to output the specific row that fails the constraint? It's good for debugging. Otherwise, I don't know which row has this problem.

mysqlimport: Error: 1452, Cannot add or update a child row: a foreign key constraint fails (`bwordDS`.`Category_Term`, CONSTRAINT `FK_qn1crq26sr0hqm5q2p7nfvdu1` FOREIGN KEY (`categories_id`) REFERENCES `Category` (`id`)), when using table: Category_Term

推荐答案

首先检查类别表是否存在于您要导入此数据的位置.

First check if Category table exist where you are trying to import this data.

如果存在类别表，则需要检查此表中的所有category_id应该以id的形式存在于类别表中.

if Category table exists then need to check that all category_id in this table should exist in Category table as id.

更好的选择是先导入类别表，然后再导入此表.

Better option is to import first category table and then this table.

首先，应先导入所有父表数据，然后再导入子表.

In generic first all parent tables data should import then child tables.

下面是一种肮脏的方式，不推荐使用-

A dirty way is as per below which is not recommended-

set foreign_key_checks=0;
import data here;
set foreign_key_checks=1;

只需知道哪个行在造成问题-

下面的查询将为您提供有问题的行.

Below query will provide you problematic rows.

SELECT a.category_id FROM Category_Term a 
  LEFT JOIN Category b ON a.category_id=b.id 
  WHERE b.id IS NULL;

注意:假设将对category_term中的category_id和类别表中的id进行索引.

Note: Assuming category_id in category_term and id in category tables will be indexed.

这篇关于找出哪一行在MySQL中添加约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！