MySQL "Cannot add or update a child row: a foreign key constraint fails"

Question

I'm new to MySQL and databases in general. I've been tasked with manually moving an old database to a new one of a slightly different format. The challenges include transferring certain columns from a table in one database to another database of a similar format. This is made further difficult in that the source database is MyISAM and the destination is InnoDB.

So I have two databases, A is the source and B is the destination, and am attempting to copy 'most' of a table to a similar table in the destination database.
Here is the command I run:

INSERT INTO B.article (id, ticket_id, article_type_id,   
  article_sender_type_id, a_from, a_reply_to, a_to, a_cc, a_subject,
  a_message_id, a_in_reply_to, a_references, a_content_type, a_body,
  incoming_time, content_path, valid_id, create_time, create_by,change_time,
  change_by)
SELECT id, ticket_id, article_type_id, article_sender_type_id,
  a_from, a_reply_to, a_to, a_cc, a_subject, a_message_id, a_in_reply_to,
  a_references, a_content_type, a_body, incoming_time, content_path,  
  valid_id, create_time, create_by, change_time, change_by 
FROM A.article 
WHERE id NOT IN ( 1 );

Error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`helpdesk`.`article`, CONSTRAINT `FK_article_ticket_id_id` FOREIGN KEY (`ticket_id`) REFERENCES `ticket` (`id`))

The reason for making the command so wordy is that the source has several columns that were unnecessary and so were pruned out of the destination table. The WHERE id NOT IN ( 1 ) is there so that the first row is not copied (it was initialized in both databases and MySQL throws an error if they both have the same 'id' field). I can't tell by the error if it expects 'ticket_id' to be unique between rows, which it is not, or if it is claiming that a row does not have a ticket_id and so can not be copied which is what the error seems to most often be generated by.

I can post the tables in question if that will help answer, but I am unsure of the best way to do that, so some pointing in the right direction there would be helpful as well.

Posts I looked at before:

For forming the command

For looking at this error

Thanks!

score 1 · Accepted Answer · answered Apr 01 '15 at 15:32

You'll want to run a SHOW CREATE TABLE on your destination table:

SHOW CREATE TABLE `B`.`article`;

This will likely show you that there is a foreign key on the table, which requires that a value exist in another table before it can be added to this one. Specifically, from your error, it appears the field ticket_id references the id field in the ticket table. This introduces some complexity in terms of what needs to be migrated first -- the referenced table (ticket) must be populated before the referencing table (article).

Without knowing more about your tables, my guess is that you haven't migrated in the ticket table yet, and it is empty. You'll need to do that before you can fill in the B.article table. It is also possible that your data is corrupt and you need to find which ticket ID is present in the article data you're trying to send over, but not present in the ticket table.

Another alternative is to turn off foreign key checks, but if possible I would avoid that, since the purpose of foreign keys is to ensure data integrity.

Thanks for the bit about turning off foreign key checks. There were some intertwined entries that couldn't be added unless I did that, and I'll probably do that for the whole database. — Michael, Apr 03 '15 at 20:02

MySQL "Cannot add or update a child row: a foreign key constraint fails"

1 Answers1