You are here

InnoDB Foreign Key error detected

MySQL (InnoDB, PBXT, NDB, TokuDB) support Foreign Keys to show relatations between tables. Those relations can be enforced through Foreign Key Constraints.

Foreign Key Constraint Errors are always either a bug in your application (which should be fixed) or inconsistencies in your data (which should be fixed) or both (first fix the bug in the application, then clean-up your data).

Foreign Key Constraint Errors can be found with the following command:

SHOW ENGINE INNODB STATUS\G

SHOW ENGINE Syntax

Foreign Key Errors look like this:

2015-08-19 15:09:19 7fbb6c328700 Transaction:
TRANSACTION 543875059, ACTIVE 0 sec inserting
mysql tables in use 1, locked 14 lock struct(s), heap size 1184,
2 row lock(s), undo log entries 1
MySQL thread id 124441421, OS thread handle 0x7fbb6c328700,
query id 7822461590 192.168.1.42 fronmdual update
INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130)
Foreign key constraint fails for table `test`.`contact`:
, CONSTRAINT `FK_contact_user_2` FOREIGN KEY (`contact_id`)
REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `contact_id` tuple:
DATA TUPLE: 2 fields;
...
But in parent table `test`.`user`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 41; compact format; info bits 0
...

Using FOREIGN KEY Constraints

InnoDB and FOREIGN KEY Constraints