How to avoid the foreign key constraint error in phpmyadmin and in php?

Question

How to avoid the foreign key constraint error in phpmyadmin and in php?

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

Very good! I am doing a small practice to remember after having been studying again the php and in knowledge of the MySqli (style procedure), when creating the database and the corresponding tables. I have never seen this error after making the form to add records from a table and then fill it out and this appears:

Editarusuario.php

<?php
$msg = $Usuario = $Nombre = $Apellido = $ID_perfil = $ID_estado = $password = $fecha_actual = NULL;

if(isset($_POST['editar'])) {
	$Usuario = $_POST['usuario'];
	$Nombre = $_POST['apellido'];
	$ID_perfil = $_POST['id_perfil'];
	$ID_estado = $_POST['id_estado'];
	$password = $_POST['password'];
	$fecha_actual = $_POST['fecha'];

	if ($Usuario && $Nombre && $ID_perfil && $ID_estado && $password && $fecha_actual) {

		$link = mysqli_connect("localhost", "root", "", "webpractica");

		if(mysqli_connect_errno()) {
			printf("Falló la conexión: $s\n", mysqli_connect_error());
			exit();
		}

		$query = mysqli_query($link, "UPDATE Usuario SET Usuario = '$Usuario', Nombre = '$Nombre', password = '$password', Fecha_Creacion = '$fecha_actual' WHERE ID_perfil = '$id_perfil' AND ID_estado = '$id_estado')");
		if(!$query) {
			printf("Error: %s\n", mysqli_error($link));
		} else {
			$msg = "Los datos se editaron correctamente";
		}
	}
}
echo $msg;
?>

This error has something related by the relation of the tables or is it the query or is it allowed to modify the restrictions of the Foreign Key?

I look forward to your comments and solutions. Regards!

EDIT: After studying intensely about mysql and trying to insert the tables without the need for PHP (unless I do it right, I modified it a bit by putting AND, I did not know if it was necessary for the two foreign keys and the error is shown in new images, the database is updated with PK and FK.)

EDIT 2: I discovered that when cutting the relation of the user table with the appendix, it works with the other tables, but when I left filling the table with php everything was fine, but, I was trying to connect the relation id_status of the user table with id_estado of annex, pulls the same error. (FK with FK) I leave the details of the contents of the tables.

php mysqli phpmyadmin

asked by Baku84 02.06.2017 в 19:30

source

2 answers

3

The error is thrown because you are trying to insert a value in a column that has a foreign key constraint ( action ). Which means that you can only insert ids that are already present in the action table.

Ideally, in the php code verify that there is already the action element that you want to associate with log before inserting it. Also id_accion is an integer and you're passing it to the query as a string.

answered by 02.06.2017 в 19:41

How are environment variables retrieved in NodeJS? Does not recognize ng command

score 2 · Accepted Answer

Your problem is due to the fact that there is no acción that has the id that you are inserting with the variable $id_accion .

Check in phpmyadmin that the acción with that id exists.

Also, I see (in the diagram) that you have a relationship between log and tipo_anexo but I do not see the fk in any of the 2 tables. However I see that in log you have a fk towards anexo which is not represented in the diagram.

So, if the log table has a fk to anexo then yes, as you say, you have to have at least one record of anexo in BD (in addition to accion ) to be able to insert a log with a anexo referenced by $id_anexo . But ... if the table log is who would have the fk of tipo_anexo then at the time of inserting a log the same thing happens and you also have to have at least one record in the table tipo_anexo that you can reference

Greetings.