0

Trying to do a simple insert into statement but getting an foreign key relationship error.

insert into orders (userId, orderDate, shippingDate)
values('xyz123', now(), now());

The error I am getting is "Cannot add or update a child row:a foreign key constraint fails ('example_1010.orders,CONSTRAINTorders_ibfk1FOREIGN KEY (userId) REFERENCESusers(userid`))"

I think I need to use an "in clause" to bypass the constraint but I don't think I am using it correctly.

insert into orders (userId, orderDate, shippingDate)
values('xyz123', now(), now())
in (select userId from users);
Charlie
  • 31
  • 6

1 Answers1

0

Just disable the foreign key

SET FOREIGN_KEY_CHECKS=0;

you don't need any other thing to add. your query is perfect

insert into orders (userId, orderDate, shippingDate)
values('xyz123', now(), now());

after that again enable foreign key

SET FOREIGN_KEY_CHECKS=1;

You should add that user_id in your parent table(users) as suggested by @drew

jai dutt
  • 780
  • 6
  • 13
  • 1
    so insert junk basically? – Drew Nov 11 '16 at 05:53
  • this case happens many time in a life of dba. we have to do this type of correction sometime – jai dutt Nov 11 '16 at 05:55
  • 2
    Fix the data where it belongs (the parent) first. Then fix the child. Otherwise you screw up your referential integrity and shouldn't even have it. – Drew Nov 11 '16 at 05:57