On a FK constraint failure - WHICH KEY?

Question

Just hit the issue again - manually inspected the data and figured it out, but this has been an issue on many occasions. The situation is, you get a FK constraint failure. You know what constraint it is from the error because it tells you that.

But, it does not tell you what key failed the constraint. Why?! And how do I get this data? I shouldn't have to go digging through data to figure out which key it was that failed the constraint.

I don't think there's any way to get any more details than what's in the error message. Welcome to the wonderful world of MySQL. — Barmar, Jun 29 '20 at 20:52
There is a way to get more MySQL FK info. But absolute basic debugging says, chop your code until the error goes away. PS. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 29 '20 at 21:11
Does this answer your question? [MySQL Cannot Add Foreign Key Constraint](https://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint) — philipxy, Jun 29 '20 at 21:12

Bill Karwin · Accepted Answer · 2020-06-29T21:20:05.047

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)

So far so good, we demonstrate that we can insert into a table with two foreign keys and if we use values that exist in the referenced tables, it's okay.

Now another row with values that don't exist:

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"))

The error tells you exactly which foreign key constraint failed.

If you want the know the value that failed, review the innodb status:

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     /  ;;

...

That spells it out pretty clearly.

If you're getting the error when you are adding a foreign key constraint to tables that already have data, and the problem is you have some values in a child table that don't have a matching value in the referenced table, you can use OUTER JOIN to find them:

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 |
+---------+-----------+

Thanks for this. I know I've actually used this before for something similar and totally forgot about it. I'd still argue that MySQL's default error message should include the key that fails the constraint in the message instead of having your dig into innodb log internals. This also means that debugging on remote dbs becomes significantly more complex where, including the key in the original error message, would generally provide enough information to resolve the issue. — Jacob Thomason, Jun 29 '20 at 22:51

On a FK constraint failure - WHICH KEY?

1 Answers1