MySQL Foreign Key Constraint Fail

328 views Asked by At

So, to clarify things: I know why this is failing.
The question is: How to work around that?
This is my setup:
enter image description here
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, CONSTRAINTdataFOREIGN KEY (id) REFERENCESuser_data(id) ON DELETE NO ACTION ON UPDATE NO ACTION)

1

There are 1 answers

1
Moppo On BEST ANSWER

You are setting the foreign key on the wrong table.

you have to set the foreign key on the field id of the table user_data referencing the filed id on the table users

Now you are trying to insert the data in the table users, but, as the id should be present in the table user_data (since you have a foreign key in the table user referencing the table user_data) it gives you the error