Laravel MySQL Foreign Key constraint fails

Question

I've started learning Laravel, and have created 3 migration files:

• Users
• Usergroups
• Users_usergroups

Users contains User information, usergroups contain usergroup information and users_usergroups is a pivot table to store which users are in which usergroups.

This is a basic example of my schema:

 Users:
 - id
 - global_user_id
 - username

 Usergroups:
 - id
 - group_name 

 - Users_usergroups:
 - id
 - user_id
 - group_id

And after reading this article: http://daylerees.com/codebright/eloquent-relationships I have the following foreign keys:

Users:
- global_user_id references user_id on users_usergroups

Usergroups:
- id references group_id on users_usergroups

Users_usergroups:
- user_id references global_user_id on Users
- group_id references id on Usergroups

Whenever I try to add a record to Users or Usergroups I get the error:

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

After reading Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails I understand that I can't add a user, because a corresponding record doesn't exist in users_usergroups, and I can't add a usergroup because of the same reason, and I can't create a record in users_usergroups if the user or usergroup hasn't been created.

So my question is, if these 3 tables reference each other, how can I add records? Am I supposed to add foreign keys to all tables?

I know from the SO question above that I can specify the check_foreign_keys attributes, but according to the comments, that's a bad idea.

Answer 1

So after fiddling around with PHPMyAdmin and laravel, It looks like only the users_usergroups table should have the foreign keys, and not the Users or Usergroups tables.

I guess that makes sense somewhat, but if I'm wrong, feel free to post an alternative answer.

Answer 2

Each foreign key has to reference a key in the main tables.

You only need this foreign key relationship: Usergroups:

Users_usergroups: - user_id references global_user_id on Users - group_id references id on Usergroups

Think of Users_usergroups as your connector table between users and groups.

BTW I'm not sure you need the 'global_user_id' as the 'id' field on Users table should be enough.