sqlalchemy.exc.AmbiguousForeignKeysError: How to define two foreign keys referencing to the same primary key in Flask SQLAlchemy?

Question

I'm creating a scorekeeping API with Flask. Despite hours of googling I haven't managed to get the database to work.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///test.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)


class Person (db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), nullable=False)
    first_name = db.Column(db.String(64), nullable=False)
    last_name = db.Column(db.String(64), nullable=False)
    birthdate = db.Column(db.String(64), nullable=True)
    description = db.Column(db.String(256), nullable=True)

    match1 = db.relationship("Match", back_populates="person")
    match2 = db.relationship("Match", back_populates="person2")



class Match (db.Model):
    id = db.Column(db.Integer, primary_key=True)
    game = db.Column(db.String(64), db.ForeignKey("game.id"))
    place = db.Column(db.String(64), nullable=True)
    time = db.Column(db.String(32), nullable=True)
    player1_id = db.Column(db.Integer, db.ForeignKey("person.id"))
    player2_id = db.Column(db.Integer, db.ForeignKey("person.id"))
    player1_score = db.Column(db.Float, nullable=False)
    player2_score = db.Column(db.Float, nullable=False)
    comment = db.Column(db.String(256), nullable=True)

    games = db.relationship("Game", back_populates="matches")
    person = db.relationship(
        "Person",
        foreign_keys="Match.player1_id",
        back_populates="match1"
    )

    person2 = db.relationship(
        "Person",
        foreign_keys="Match.player2_id",
        back_populates="match2"
    )


class Game (db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=True)
    score_type = db.Column(db.Integer, nullable=False)

    matches = db.relationship("Match", back_populates="games")

In a Python shell:

>>> from app import *
>>> db.create_all()
>>> pers1 = Person(username="username",first_name="user",last_name="name")

Traceback (most recent call last):
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py", line 2354, in _determine_joins
    consider_as_foreign_keys=consider_as_foreign_keys,
  File "<string>", line 2, in join_condition
  File "<string>", line 2, in _join_condition
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 130, in warned
    return fn(*args, **kwargs)
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/sql/selectable.py", line 923, in _join_condition
    a, b, constraints, consider_as_foreign_keys
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/sql/selectable.py", line 1040, in _joincond_trim_constraints
    "join explicitly." % (a.description, b.description)
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 
'person' and 'match'; tables have more than one foreign key constraint 
relationship between them. Please specify the 'onclause' of this join 
explicitly.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 2, in __init__
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 373, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 855, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 199, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 297, in __call__
    fn(*args, **kw)
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3341, in _event_on_first_init
    configure_mappers()
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3229, in configure_mappers
    mapper._post_configure_properties()
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 1947, in _post_configure_properties
    prop.init()
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/interfaces.py", line 196, in init
    self.do_init()
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py", line 1854, in do_init
    self._setup_join_conditions()
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py", line 1934, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped,
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py", line 2239, in __init__
    self._determine_joins()
  File "/home/USERNAME/pythonvenvs/pwp_3.6/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py", line 2399, in _determine_joins
    "foreign key reference to the parent table." % self.prop
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Person.match1
- there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which 
should be counted as containing a foreign key reference to the parent table.

How could I get both Match.player1_id and Match.player2_id to reference Person.id? There are a lot of examples in the web but I have had success with none of those. Should I use backref or back_populates?

I have tried having the relationships in the Person table in just one relationship with the foreign_keys parameter being a list. I couldn't get that to work, either.

Just as the error states, the relationships in `Person` have to define the `foreign_keys` to use as well. `backref` or `back_populates` does not relate to that. They control the linking / "bidirectional" behaviour at the ORM level between relationship attributes in different models: https://docs.sqlalchemy.org/en/13/orm/backref.html. — Ilja Everilä, Feb 14 '20 at 05:22

score 0 · Answer 1 · answered Feb 13 '20 at 19:21

I tried your example and here is how I made it work (but I am not sure if this is what you are looking to achieve).

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///test.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)


class Person (db.Model):
    __tablename__ = 'person'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), nullable=False)
    first_name = db.Column(db.String(64), nullable=False)
    last_name = db.Column(db.String(64), nullable=False)
    birthdate = db.Column(db.String(64), nullable=True)
    description = db.Column(db.String(256), nullable=True)

    match1 = db.relationship(
        "Match",
        foreign_keys="Match.player1_id",
        backref ="player1"
    )

    match2 = db.relationship(
        "Match",
        foreign_keys="Match.player2_id",
        backref="player2"
    )


class Match (db.Model):
    __tablename__ = 'match'
    id = db.Column(db.Integer, primary_key=True)
    game = db.Column(db.Integer, db.ForeignKey("game.id"))
    place = db.Column(db.String(64), nullable=True)
    time = db.Column(db.String(32), nullable=True)
    player1_id = db.Column(db.Integer, db.ForeignKey("person.id"))
    player2_id = db.Column(db.Integer, db.ForeignKey("person.id"))
    player1_score = db.Column(db.Float, nullable=False)
    player2_score = db.Column(db.Float, nullable=False)
    comment = db.Column(db.String(256), nullable=True)

    games = db.relationship("Game", back_populates="matches")


class Game (db.Model):
    __tablename__ = 'game'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=True)
    score_type = db.Column(db.Integer, nullable=False)

    matches = db.relationship("Match", back_populates="games")

sqlalchemy.exc.AmbiguousForeignKeysError: How to define two foreign keys referencing to the same primary key in Flask SQLAlchemy?

1 Answers1