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


i'm new mysql , databases in general. i've been tasked manually moving old database new 1 of different format. challenges include transferring columns table in 1 database database of similar format. made further difficult in source database myisam , destination innodb.

so have 2 databases, source , b destination, , attempting copy 'most' of table similar table in destination database.
here command run:

insert 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  a.article  id not in ( 1 ); 

error:

error 1452 (23000): cannot add or update child row: foreign key constraint fails (`helpdesk`.`article`, constraint `fk_article_ticket_id_id` foreign key (`ticket_id`) references `ticket` (`id`)) 

the reason making command wordy source has several columns unnecessary , pruned out of destination table. id not in ( 1 ) there first row not copied (it initialized in both databases , mysql throws error if both have same 'id' field). can't tell error if expects 'ticket_id' unique between rows, not, or if claiming row not have ticket_id , can not copied error seems generated by.

i can post tables in question if answer, unsure of best way that, pointing in right direction there helpful well.

posts looked @ before:

for forming command

for looking @ error

thanks!

you'll want run show create table on destination table:

show create table `b`.`article`; 

this show there foreign key on table, requires value exist in table before can added one. specifically, error, appears field ticket_id references id field in ticket table. introduces complexity in terms of needs migrated first -- referenced table (ticket) must populated before referencing table (article).

without knowing more tables, guess haven't migrated in ticket table yet, , empty. you'll need before can fill in b.article table. possible data corrupt , need find ticket id present in article data you're trying send over, not present in ticket table.

another alternative turn off foreign key checks, if possible avoid that, since purpose of foreign keys ensure data integrity.


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -