MySQL Forums :: Foreign Keys (Referential Integrity) :: Insert fails: error 1452, a foreign key constraint fails

I've encountered a weird infrequent problem with foreign key index as follows (5.5.9):

I have three tables: Text, KeywordToText and Text to represent n-m relations between text and keywords. PK are: uuid, (textUUID, keywordUUID) and uuid respectively.

select * from Text where uuid = 21198002

uuid, text
----------------
'21198002', 'relation'


select * from Keyword where uuid = 1902174

uuid, keyword
----------------
'1902174', 'relation'

Then,

INSERT INTO KeywordToText (keywordUUID, textUUID) VALUES (1902174, 21198002);

Error Code: 1452
Cannot add or update a child row: a foreign key constraint fails (`liveartsdev`.`KeywordToText`, CONSTRAINT `fk_KeywordToText_Keyword1` FOREIGN KEY (`keywordUUID`) REFERENCES `Keyword` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION)

I've also gotten:

Error Code: 1452
Cannot add or update a child row: a foreign key constraint fails (`liveartsdev`.`KeywordToText`, CONSTRAINT `fk_KeywordToText_Text1` FOREIGN KEY (`textUUID`) REFERENCES `Text` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION)

However, if I delete these foreign key constraints, and add them again, the INSERT works fine.

It seems that the indexes are corrupted, and somehow lost track of the PK values?



Edited 1 time(s). Last edit at 02/15/2011 07:48PM by Ken Iisaka.