时间戳

首页 > 解决方案 > 在 FK 约束失败时 - WHICH KEY？

mysql - 在 FK 约束失败时 - WHICH KEY？

问题描述

再次遇到问题 - 手动检查数据并弄清楚，但这在很多情况下都是一个问题。情况是，您会遇到 FK 约束失败。您知道错误的约束是什么，因为它告诉您。

但是，它不会告诉您哪个键未通过约束。为什么？！我如何获得这些数据？我不应该去挖掘数据来找出哪个键没有通过约束。

标签： mysqlforeign-keys

解决方案

mysql> create table books (book_id int primary key);
mysql> insert into books set book_id = 22;

mysql> create table authors (author_id int primary key);
mysql> insert into authors set author_id = 44;

mysql> create table book_authors (book_id int, author_id int, 
  primary key (book_id, author_id), 
  foreign key (book_id) references books (book_id), 
  foreign key (author_id) references authors (author_id));

mysql> insert into book_authors values (22, 44);
Query OK, 1 row affected (0.01 sec)

到目前为止一切顺利，我们演示了我们可以使用两个外键插入到一个表中，如果我们使用引用表中存在的值，那没关系。

现在另一行的值不存在：

mysql> insert into book_authors values (22, 66);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  ("test"."book_authors", CONSTRAINT "book_authors_ibfk_2" FOREIGN KEY ("author_id") 
  REFERENCES "authors" ("author_id"))

mysql> insert into book_authors values (33, 44);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
  ("test"."book_authors", CONSTRAINT "book_authors_ibfk_1" FOREIGN KEY ("book_id") 
  REFERENCES "books" ("book_id"))

该错误准确地告诉您哪个外键约束失败。

如果您想知道失败的值，请查看 innodb 状态：

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2020-06-29 14:02:27 0x70000cf55000 INNODB MONITOR OUTPUT
=====================================
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-06-29 14:01:41 0x70000cf55000 Transaction:
TRANSACTION 557501, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 64, OS thread handle 123145519714304, query id 82122 localhost root update
insert into book_authors values (33, 44)
Foreign key constraint fails for table "test"."book_authors":
,
  CONSTRAINT "book_authors_ibfk_1" FOREIGN KEY ("book_id") REFERENCES "books" ("book_id")
Trying to add in child table, in index PRIMARY tuple:
DATA TUPLE: 4 fields;
 0: len 4; hex 80000021; asc    !;;
 1: len 4; hex 8000002c; asc    ,;;
 2: len 6; hex 0000000881bd; asc       ;;
 3: len 7; hex bb000001330110; asc     3  ;;

But in parent table "test"."books", in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000016; asc     ;;
 1: len 6; hex 0000000881b8; asc       ;;
 2: len 7; hex b70000012f0110; asc     /  ;;

...

这很清楚地说明了这一点。

如果在向已有数据的表添加外键约束时遇到错误，并且问题是子表中有一些值在引用的表中没有匹配值，则可以使用OUTER JOIN 找到它们：

mysql> alter table book_authors drop foreign key book_authors_ibfk_1;
mysql> alter table book_authors drop foreign key book_authors_ibfk_2;

mysql> insert into book_authors values (33, 44);
mysql> insert into book_authors values (22, 66);

mysql> select ba.* from book_authors as ba 
  left outer join books as b using (book_id) 
  where b.book_id is NULL;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|      33 |        44 |
+---------+-----------+

mysql> select ba.* from book_authors as ba 
  left outer join authors as a using (author_id) 
  where a.author_id is NULL;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|      22 |        66 |
+---------+-----------+

推荐阅读