In MySQL I have 2 tables, the table ciudadano
is related to the other examen
; exam has the field examen.Cedula_Ciudadano
that comes from ciudadano.Cedula
.
How do I update a record from citizen and at the same time update it in exam? Why does it send me the error:
error 1452. Cannot add or update a child row: a foreign key constraint fails
The code is the following:
UPDATE
examen e
JOIN ciudadano c
on e.Cedula_Ciudadano=c.Cedula
SET
e.Cedula_Ciudadano='s',
c.Cedula='s'
WHERE
c.Cedula='222-301000-11023'
In general, it is not a good idea to use a field that is not fixed over time as a primary key. For example, in the table
Ciudadano
, it would be a better implementation to create an auto-incrementing field in the following way:id_ciudadano INT NOT NULL AUTO_INCREMENT
, and add the ID as one more value. This way, theid_ciudadano
would never change, and could be a perfect primary key, not having to implement any artifice described in this or the other answers.Possible solution
The error
Cannot add or update a child row: a foreign key constraint fails
appears when:From what you're saying, I assume you're trying to change the citizen's ID. Most likely, said citizen has already registered an exam in the corresponding table, and for this reason it throws the error.
You can do two things:
CONSTRAINT
your foreign key.Remove referenced dependencies
Suppose we have the citizen with an ID
1234
, and said user has two exams in the tableexamen
. All you would have to do is run this command:If what you want is to keep the exams of said citizen, save the exam data and insert them later with the new ID. If you're doing it from a script, it's an easy task. If you do it from a scheduled service, most languages provide functions to make insert tasks quite easy.
Add specifications to CONSTRAINT
When creating a foreign key, in MySQL by default the following specifications are added:
All you have to do is change these settings so that when you change the citizen's ID, the references in the exams are also changed. Negative point : It can only be done at the time of creating the table, for example:
When the table has been created and already has data, it would have to be dropped and recreated ( see this answer for more information )
I had the same error and was able to fix it with...
you can also use...
but the first option is safer since it is only active for the duration of the session
Sources: ERROR 1452: Cannot add or update a child row: a foreign key constraint fails