无法添加或更新外键约束失败的子行 [英] Cannot add or update a child row a foreign key constraint fails

问题描述

表1:

表2:

CREATE TABLE `tbl_order` (
  `order_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `cus_fullname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `total_price` double DEFAULT NULL,
  `active` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tbl_payment` (
  `pay_id` int(50) NOT NULL,
  `pro_id` int(15) NOT NULL,
  `pay_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_adress` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_cardname` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_cvc` int(3) NOT NULL,
  `pay_number` text COLLATE utf32_unicode_ci NOT NULL,
  `pay_mm` int(2) NOT NULL,
  `pay_yyyy` int(4) NOT NULL,
  `pay_totals` varchar(255) COLLATE utf32_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;

推荐答案

这种情况通过以下两种方式之一发生:

That happens in one of two ways:

1-您正尝试向子表中添加一条记录，并且使用了父表主键中不存在的外键值.

1- You're trying to add a record to the child table and you used a value for the foreign key that doesn't exist in the parent table's primary key.

在这种情况下，请仔细检查该值是否存在于父表中.也许您正在输入父记录和子记录，但是父记录添加失败.在这种情况下，您必须停止操作，而不是忽略错误并继续添加子项.

In this case, double check that the value exists in the parent table. Perhaps you're entering a parent and children records, but the parent record failed to be added. You'll have to stop in this case instead of ignoring the error and continuing with adding the children.

2-您的表没有像ON DELETE CASCADE那样的引用完整性，并且您从父表中删除了一条记录，然后尝试从子表中更新一条记录，该记录的外键引用了从中删除记录.父表.

2- Your tables don't have referential integrity like ON DELETE CASCADE and you deleted a record from the parent table, then you're trying to update a record from the child table that has its foreign key referencing the delete record from the parent table.

在这种情况下，您可能希望删除子项而不是对其进行更新.考虑将参照完整性添加到表中.

In this case, you may want to delete the children instead of updating them. Consider adding referential integrity to your tables.

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