Cannot add or update a child row: a foreign key constraint fails

[iliji]

So I’ve been trying to insert data into a table called poubelle through a web page but each time I received this error but I don’t understand why I’m getting this error thats prevents me from inserting data. Any help is welcome and thanks in advance.

Error: INSERT INTO poubelle VALUES (PID = 1, uid = 1);
Cannot add or update a child row: a foreign key constraint fails (poubelle_connecter.poubelle, CONSTRAINT fk_uid FOREIGN KEY (uid) REFERENCES enregistrer (uid))

The structure of table enregistrer is:

and for the table poubelle is
PID INT(11) PRIMARY KEY
uid INT(11) FOREIGN KEY REFERENCES enregistrer(uid)

[skawid]

The foreign key constraint here prevents you adding a uid into poubelle if that uid does not already exist in enregistrer. You need to add the record to enregistrer before you reference it.

[iliji]

Thanks for answering my qyestion.
I had already added some datas in the enregistrer table before trying to add data in the poubelle table. But still it won’t work.

Another thing I want to add, is the PID from the poubelle table is also a foreign key in 3 other tables where datas has been already inserted and the PID in those table already has some values. Does these values might block insertion in the poubelle table?

[ErnieAlex]

You can not enter the UID field. It is auto-incremented which means it is created by the DB system.
Just leave that out of your INSERT and you should be all set.

[iliji]

From which table you mean? If you mean enregistrer where the uid is autoincrement, this I know and the table contains data already inserted previously. My concern is how I can save the same uid data from enregistrer into poubelle. The value of uid is stored as session variable on the site.

[ErnieAlex]

Well, any table! If you have a table with an id in it and the id is set to auto-increment, you can NOT insert the id. The database will change it to whatever it wants or throw it out as an error. Just can not be done.

You either mush remove the auto-increment and then you can insert the id number, but that defeats the way that databases work. Normally, you let the database create the id number using auto-increment and just insert the data, not the id.

Now, you do not have an id, you have some other name for it, uid. If the “uid” is used as an index, you would never insert it. If you use this uid number as something that is entered by the user or from a program, then just remove the auto-increment from it.

Also, you can not have a foreign key referencing the same table. Does not make any sense. Perhaps you do not understand indexing? Your post mentions PID as the primary key, but you do not have that field in your list. Only UID. And, that is set to auto-increment which makes it the primary key.

I am confused on what you are attempting to do.

[iliji]

Thanks for the help, for the id I can use a function in php to generate a unique ID for each new user though it defeqt the purpose to do that.

Secondly, at the time I posted this question, the site didn’t allow me to post 2 pictures, so the other table, I had to write it in normal text. For the foreign key parts, I’m not referencing the same table, uid is the primary key in the table enregistrer and PID is the primary key in another table, poubelle, and I’m linking both table using uid as foreign key in poubelle’s table.

[ErnieAlex]

Thanks for explaining. So, in standard use of a database table, the first field is usually “id”.
This is usually set to INT(11) or INTEGER size of 11. But, can be something else if needed.
Also, this “id” is set to auto-increment. So, every inset for this table, an new and unique id is created.
Automatically. You have the “uid” set to auto-increment. You can not insert data into a field that has
been set to auto-increment. Therefore, if you want to use PHP to create a uid field for yourself, you can
just take off the auto-increment option. You would still need to add as the first field one called “id” and
set THAT one to auto-increment. In this manner, you will be able to still have an unique id for each row.

Now, with that said, to insert data into the “uid” filed, just remove the auto-increment. You are re-inventing the wheel, as they say. You can get a unique index without using PHP to create it. BUT, if you have a use for a uid field, just also create a in field. Hope that makes sense to you!

[iliji]

So I did what you ask, putting id as auto-increment in the enregistrer table and also make it the primary key but the problem still persist.

[ErnieAlex]

Did you remove the auto-increment from the uid field?

[iliji]

yes I did remove the auto-increment Below table for enregistrer

and below poubelle table.

[ErnieAlex]

Okay, what are the two queries you are using to insert data?
You can not cross-insert. You have to insert data into each of them separately.

[ErnieAlex]

You can set up a query to do two inserts…

INSERT INTO enregister VALUES (uid = 1);
INSERT INTO poubelle VALUES (PID = 1);

Basically, one QUERY, but, two inserts in the query.

Leaving for the night… Good luck!

[iliji]

basically first the user create an account to log in. Then on another page in the dashboard he shall add a PID for a particular dustbin. Below is the code.

<?php
if(!isset($_SESSION)) {
  session_start();
  $uid = $_SESSION["uid"];
}


require 'db.php';

if (isset($_POST["ajouter"])) {
  $pid = $_POST["pid"];
  $sql = "INSERT INTO poubelle(PID, uid) VALUES (PID = $pid, uid = $uid);";
  if (mysqli_query($conn, $sql) === TRUE) {
    $_SESSION['uid'] = $uid;
    $_SESSION['PID'] = $PID;
    header("location: ../html/dash.php?success=insertionSuccess");
  }
  else {
    echo "Error: " . $sql . "<br>" . $conn->error;
  }
  $conn->close();
}
 ?>

[iliji]

Thanks good night to you

[ErnieAlex]

@iliji

$sql = "INSERT INTO poubelle (PID, uid) VALUES ($pid, $uid)";

You just need to remove the extra stuff in VALUES…
Goodnight…

[iliji]

Thanks, I feel dumb, it works. Been laughing at myself since morning since the error was too dumb.