[SOLVED] Copy column content from one table to another mysql

Copy column content from one table to another mysql

I am trying to populate column s_phone_mobile from oc_t_user table to oc_t_item table in mysql database. I use the following query:

INSERT INTO `oc_t_item` (s_phone_mobile) 
SELECT s_phone_mobile
FROM   oc_t_user;

But I get the following error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`poljo_test`.`oc_t_item`, CONSTRAINT `oc_t_item_ibfk_2` FOREIGN KEY (`fk_i_category_id`) REFERENCES `oc_t_category` (`pk_i_id`))

I realize this may be duplicate question, but every "trick" I tried did not work for me.

Solution

Your problem is being caused by your insert specifying only a value for s_phone_mobile, effectively specifying NULL for the three foreign keys which this table contains.

A foreign key constraint in MySQL will ensure that your insert refers to foreign keys which either exist, or possibly allowing NULL if you defined the keys to be nullable. My guess is that at least one of the following keys is not nullable:

fk_i_user_id
fk_i_category_id
fk_c_currency_code

So, to fix this problem, you should also insert foreign key values for the above three keys, unless a key allow NULL and you are OK with that. And make sure that the values specified actually correspond to records in the respective parent tables.