Hello guys,
I have just started using Phoenix and I’m having a problem with the migrations.
I want to create a table called “User” that has a references “player_id” in table Players. But “Player” also has a reference “user_id” for User.
When I run my migration it either says “ERROR 42P01 (undefined_table): relation “players” does not exist.” or “ERROR 42P01 (undefined_table): relation “users” does not exist.” dependending on which one come first.
What is the solution to this ?
Thanks in advance!
Hello,
are you pretty sure that you want to do this? A better approach could be:
- Keep the reference in only one of the tables, as a “belongs to” relationship.
- You could use a third table that references both
User and Player, as a “many to many” relationship.
If they have a circular reference, they might be the same entity.
However if you really want to do that, create the two migrations for User and Player without references, then in a third migration you can create the reference indexes.
Best
Agreeing with @rms.mrcs here, this is generally a pattern you want to avoid in SQL. Even if you successfully create the database tables with the constraints, it gets incredibly hard to actually insert any rows. You have to always use transactions and then defer the constraints, otherwise you have the impossible situation of trying to enter a player for a user that doesn’t exist yet, or a user for a player that doesn’t exist yet.
1 Like
Also for note, this is an Ecto question, not a Phoenix question, and as I didn’t see anything phoenix related in the OP or any later posts, moving the thread. 
