So, to clarify things: I know why this is failing.
The question is: How to work around that?
This is my setup:
The referencing table is users and the referencing column is id, the referenced table is user_data, and the referenced column is id.
I want to store the email, username, salt and password in one table, but the user data like login tokens and such in another. I thought this was the right way to do it. Apparently I am missing something.
And oh I'm using PHP with PDO.
function registerUser($email, $username, $password)
{
global $db;
try {
$prep = $db->prepare("INSERT INTO users (email, username, salt, password) VALUES(':email', ':username', ':salt', ':password')");
$salt = "abcd";
$prep->bindParam(':email', $email);
$prep->bindParam(':username', $username);
$prep->bindParam(':salt', $salt);
$prep->bindParam(':password', $password);
$prep->execute();
echo "Success";
} catch (PDOException $e) {
die("Query failed: " . $e->getMessage());
}
}
EDIT: Forgot the error...
Query failed: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (
infokup2016.
users, CONSTRAINT
dataFOREIGN KEY (
id) REFERENCES
user_data(
id) ON DELETE NO ACTION ON UPDATE NO ACTION)
You are setting the foreign key on the wrong table.
you have to set the foreign key on the field
id
of the tableuser_data
referencing the filedid
on the tableusers
Now you are trying to insert the data in the table
users
, but, as theid
should be present in the tableuser_data
(since you have a foreign key in the tableuser
referencing the tableuser_data
) it gives you the error