David David - 5 months ago 12
MySQL Question

cannot update foreign key in different session mysql

I have a problem updating a field in a table when i'm in a new session. I have this two tables:

Pedidos

Usuarios

I want to update the idusuario from the pedidos table to reference a different usuario.
I have tried inserting the idusuario fk as null but it neither works

If I execute the update in the same session where I have created the tables there is no problem. See image.

enter image description here

But if I try to execute the same query in another session it crashes with the next error code: (also tried on command line console)

Error shown on command line console

Answer

You should drop your foreign key , update , and then re add it :

ALTER TABLE pedidos
 DROP FOREIGN KEY FK_name;

UPDATE pedidos set idusuario = 4 where id_pedidos = 2;

ALTER TABLE pedidos 
ADD CONSTRAINT FK_name FOREIGN KEY (IdUsuario) REFERENCES SecondTable(idPedidos);