PHP/MySQL：“无法添加或更新子行：外键约束失败”

发布于 2025-01-08 16:59:52 字数 767 浏览 1 评论 0原文

我对 (My)SQL 比较陌生，并且已经用头敲打键盘两天多了。我只是似乎无法解决这个问题。我有一个包含三个表的数据库：items、coords 和 price。表项与价格具有非强制性的一对多关系，与坐标具有强制性的 1:1 关系。每当我尝试更新所有三个时，我都会收到此错误：

“无法添加或更新子行：外键约束失败

(`items`.`items`, CONSTRAINT `fk_items_coords1` FOREIGN KEY (`coords_idCoords`) REFERENCES `coords` (`idCoords`) ON DELETE NO ACTION ON UPDATE NO ACTION)

”

我已经尝试了我能找到并想到的一切：更改插入顺序，更改关系本身，首先手动添加（我知道这是必须的）等等。我也尝试过将它们都更改为强制，因为我注意到插入价格外键时不会出现这个问题。然而，我发现它只是插入了一个空值。它可能有一个非常简单的解决方案，但我就是无法理解它。

这是我的 PHP 和这是我的 SQL。

我很清楚 PHP 很草率（尤其是两个单独的查询），但是一旦我解决了这个问题，我就会解决这个问题。我希望我已经为您提供了足够的信息。如果您还有其他需要，请告诉我。提前致谢。

原文

I'm relatively new to (My)SQL and have been bashing my keyboard with my head for over two days now. I just can't seem to solve the problem. I have a database that holds three tables: items, coords, and price. The table items has a non-mandatory 1-to-many relationship with price, and a mandatory 1:1 relationship with coords. Whenever I try to update all three of them, I get this error:

"Cannot add or update a child row: a foreign key constraint fails

(`items`.`items`, CONSTRAINT `fk_items_coords1` FOREIGN KEY (`coords_idCoords`) REFERENCES `coords` (`idCoords`) ON DELETE NO ACTION ON UPDATE NO ACTION)

I've tried everything I could find and think of: changing the order of insertion, changing the relationships itself, adding manually first (I know this is a must) etc. etc. I've also tried changing them both to mandatory for a moment, as I noticed this problem doesn't occur at the insertion of the foreign key of price. I found out it just inserts a null value, however. It probably holds a really easy solution, but I just can't get my head around it.

Here is my PHP and here is my SQL.

I'm well aware that the PHP is sloppy (especially the two seperate queries), but I'll get around to that once I've fixed this. I hope I've provided you with enough information. If there's anything else you need, let me know. Thanks in advance.

分享到QQ

分享到微博