时间戳

首页 > 解决方案 > 如何调试“无法添加或更新子行：外键约束失败”？

mysql - 如何调试“无法添加或更新子行：外键约束失败”？

问题描述

当子行无法更新时，我遇到了这个问题。

下面是我使用的代码：创建了两个表PUBLISHER，然后在表为空时向表BOOK中添加了一些信息，然后当我尝试向表中添加信息时，它给了我一个错误。PUBLISHERBOOKBOOK

这是创建的 PUBLISHER 表：

MySQL [rdatabase]> `CREATE TABLE PUBLISHER (PUBLISHER_CODE CHAR (3) PRIMARY KEY, PUBLISHER_NAME CHAR (25), CITY CHAR (20));`

Query OK, 0 rows affected (0.07 sec)

这是创建的 BOOK 表：

MySQL [rdatabase]> `CREATE TABLE BOOK (BOOK_CODE CHAR (4) PRIMARY KEY, TITLE CHAR (40), PUBLISHER_CODE CHAR, FOREIGN KEY (PUBLISHER_CODE) REFERENCES PUBLISHER (PUBLISHER_CODE), TYPE CHAR (3), PRICE DECIMAL (4), PAPERBACK CHAR (1));`



 Query OK, 0 rows affected (0.10 sec)

添加到 PUBLISHER 表中的信息：

MySQL [rdatabase]> 

INSERT INTO PUBLISHER (PUBLISHER_CODE, PUBLISHER_NAME, CITY)
    -> VALUES ('WP', 'Westview Press', 'Boulder CO');

Query OK, 1 row affected (0.01 sec)

MySQL [rdatabase]> `SELECT * FROM PUBLISHER;`

+----------------+----------------+--------------+
| PUBLISHER_CODE | PUBLISHER_NAME | CITY         |
+----------------+----------------+--------------+
| AH             | Arkham House   | Sauk City WI |
| WP             | Westview Press | Boulder CO   |
+----------------+----------------+--------------+

2 rows in set (0.00 sec)

但是当我试图将信息插入BOOK表中时，它给了我以下错误：

MySQL [rdatabase]>

 INSERT INTO BOOK (BOOK_CODE, TITLE, PUBLISHER_CODE, TYPE, PRICE, PAPERBACK)
    -> VALUES ('0180', 'A Deepness in The Sky', 'TB', 'SFI', 7.19, 'Y');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`rdatabase`.`BOOK`, CONSTRAINT `BOOK_ibfk_1` FOREIGN KEY (`PUBLISHER_CODE`) REFERENCES `PUBLISHER` (`PUBLISHER_CODE`))

标签： mysql

解决方案

这是因为您在表中提到PUBLISHER_CODE了外键，Book并且表中没有带有PUBLISHER_CODEas'TB'的Publisher Publisher。外键约束在那里失败。

推荐阅读