SQLAlchemy - Multiple Foreign key pointing to same table same attribute

Question

My data base structure....

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    user = db.relationship("BankSlip", back_populates="person_user")
    reference = db.relationship("BankSlip", back_populates="person_reference")

class BankSlip(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

    person_user_id = db.Column(db.Integer, db.ForeignKey(Person.id))
    person_ref_id = db.Column(db.Integer, db.ForeignKey(Person.id))

    person_user = db.relationship("Person", back_populates="user", uselist=False, foreign_keys=[person_user_id])
    person_reference = db.relationship("Person", back_populates="reference", uselist=False, foreign_keys=[person_ref_id])

I get the following errors while running at sqlite with Flask-SQLAlchemy

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Person.user - 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.

Here is my pip freeze

appdirs==1.4.3
APScheduler==3.3.1
bcrypt==3.1.3
blinker==1.4
cffi==1.9.1
click==6.7
cssselect==1.0.1
cssutils==1.0.2
Flask==0.12
Flask-Login==0.4.0
Flask-Mail==0.9.1
Flask-Principal==0.4.0
Flask-SQLAlchemy==2.2
Flask-WTF==0.14.2
gunicorn==19.7.1
itsdangerous==0.24
Jinja2==2.9.5
lxml==3.7.3
MarkupSafe==1.0
nose==1.3.7
packaging==16.8
pkg-resources==0.0.0
premailer==3.0.1
pycparser==2.17
pyparsing==2.2.0
python-dateutil==2.6.0
pytz==2017.2
requests==2.13.0
schedule==0.4.2
six==1.10.0
SQLAlchemy==1.1.6
tzlocal==1.4
uWSGI==2.0.15
Werkzeug==0.12.1
WTForms==2.1

Edit: One BankSlip can have one user and one reference.... It should be a one-to-one relationship where parent -> child is BankSlip -> User or BankSlip -> Reference. So, a child can have multiple parents!

That still does not require `uselist=False` BankSlip side. `person_user` and `person_reference` are still one to many relationships separately, where many BankSlips can relate to one Person. If you want one to one, define `uselist=False` at Person's side. Is that meant to be so that a BankSlip can have a user relationship, or reference relationship, but not both? — Ilja Everilä, Jun 08 '17 at 12:14
Sorry, mixed the order, the relationships in BankSlip are many to one, the ones in Person one to many. Still, uselist to Person's relationships. — Ilja Everilä, Jun 08 '17 at 12:23
Thank you very much! I mixed up 121 with 12n. uselist should be True or default.... — Dewsworld, Jun 08 '17 at 12:26
Also, do you think that it's wrong example? http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-one — Dewsworld, Jun 08 '17 at 13:24
No, note that in the latter example the relationship is many to one, i.e. a child could have multiple parents (the foreign key is in the parent), so that's why the argument is on the child's relationship to make it one to one. — Ilja Everilä, Jun 08 '17 at 14:25

Ilja Everilä · Accepted Answer · 2017-06-08T11:45:58.317

Provide the required foreign_keys argument, as instructed by the error message:

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    user = db.relationship("BankSlip", foreign_keys='BankSlip.person_user_id', back_populates="person_user")
    reference = db.relationship("BankSlip", foreign_keys='BankSlip.person_ref_id', back_populates="person_reference")

With Declarative you can define the foreign keys as a string, which will help in resolving the circular dependency. Alternatively you could use backref instead of back_populates:

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)


class BankSlip(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

    person_user_id = db.Column(db.Integer, db.ForeignKey(Person.id))
    person_ref_id = db.Column(db.Integer, db.ForeignKey(Person.id))

    person_user = db.relationship("Person", backref="user", uselist=False, foreign_keys=[person_user_id])
    person_reference = db.relationship("Person", backref="reference", uselist=False, foreign_keys=[person_ref_id])

Note that you've got the uselist=False at the wrong end of the relationship, or it is redundant, since a Person can be referenced by multiple BankSlips. It belongs at Persons's side, so:

from sqlalchemy.orm import backref

...
    person_user = db.relationship("Person", backref=backref("user", uselist=False), foreign_keys=[person_user_id])
    person_reference = db.relationship("Person", backref=backref("reference", uselist=False), foreign_keys=[person_ref_id])

Thanks for your clear explanation. Docs lack of examples, and without them you cannot understand a lot of their complicated concepts. Like this concept, it's horrible, but that's SQL Alchemy, there is no better ORM. — elano7, Dec 31 '22 at 11:23

SQLAlchemy - Multiple Foreign key pointing to same table same attribute

1 Answers1

Linked