mardi 5 mai 2015

mysql php Relate tables between (migrated) full tables

I'm having an issue relating tables. Long story short I'm migrating tables from a Postgres DB to a mysql one. Since I'm using Laravel, Eloquent has made it easy to migrate the tables "users" and "groups" because they are also objects, so all I did was convert the postgres result to Json, adding each element and saving it to the laravel's mysql DB. There's a many-to-many relationship between users and groups. Postgres also contains a "user-group" table with this relation. And I've managed to keep the same id's from postgres. But the code to save it to mysql is slightly different from before (saving users and groups). Here's what I'm doing:

foreach ($jsonData as $rel) {
    DB::table('group_user')->insert(array(
        array('user_id' => $rel['user_id']),
        array('group_id' => $rel['group_id']),
    ));
}

The DB format is pretty much the same in both databases. The only difference is that group_user has his own auto-incrementing id alongside with the foreign keys for user_id and group_id

Trying to run the function mentioned above turns this result.

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a
child row: a foreign key constraint fails (`ProjectName`.`group_user`, 
CONSTRAINT `group_user_group_id_foreign` FOREIGN KEY (`group_id`) REFERENCES
`groups` (`id`)) (SQL: insert into `group_user` (`user_id`) values (1), (1))

I don't understand why, there's a user with id 1 and a group also with id 1. The table is fully functional in the postgres DB. My fear is that I'm establishing a relation with already existing data, and that might be what's causing these issues. Thank you for your time and patience.

EDIT: the corrected query goes like this btw

foreach ($jsonData as $rel) {
    DB::table('group_user')->insert(array(
        array('user_id' => $rel['user_id'], 'group_id' => $rel['group_id']),
    ));
}

Thank you very much @Mark B

Aucun commentaire:

Enregistrer un commentaire