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

8i9zcol2 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(295)

当无法更新子行时遇到此问题。
下面是我使用的代码：创建了两个表 PUBLISHER 以及 BOOK ，然后将一些信息添加到 PUBLISHER 表while BOOK 表是空的，当我尝试向 BOOK 它给了我一个错误。
这是在发布服务器表中创建的：

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)

这是创建的图书表：

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)

已添加到发布服务器表中的信息：

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

来源：https://stackoverflow.com/questions/52885803/how-to-debug-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails