Copy column content from one table to another mysql

Question

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.

Please show the table definitions for `oc_t_item` _and_ any tables with which it has a relationship through primary/foreign key. — Tim Biegeleisen, Dec 07 '16 at 08:06
yes please show me the structures. My guess is that the problem will be easy to spot with this information. — Tim Biegeleisen, Dec 07 '16 at 08:13
@TimBiegeleisen Is there any way I can insert screenshots here? I do not know how else to show what you need. Should we continue in chat maybe? — Nancy, Dec 07 '16 at 08:49
Ok, better you open chat please, because I do not know how :) — Nancy, Dec 07 '16 at 08:53
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/129982/discussion-between-tim-biegeleisen-and-nancy). — Tim Biegeleisen, Dec 07 '16 at 08:53

score 1 · Accepted Answer · answered Dec 07 '16 at 09:12

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.

Copy column content from one table to another mysql

1 Answers1