88

Am trying to setup a postgresql table that has two foreign keys that point to the same primary key in another table.

When I run the script I get the error

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

That is the exact error in the SQLAlchemy Documentation yet when I replicate what they have offered as a solution the error doesn't go away. What could I be doing wrong?

#The business case here is that a company can be a stakeholder in another company.
class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)

class Stakeholder(Base):
    __tablename__ = 'stakeholder'
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    company = relationship("Company", foreign_keys='company_id')
    stakeholder = relationship("Company", foreign_keys='stakeholder_id')

I have seen similar questions here but some of the answers recommend one uses a primaryjoin yet in the documentation it states that you don't need the primaryjoin in this situation.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
lukik
  • 3,919
  • 6
  • 46
  • 89

2 Answers2

109

Tried removing quotes from the foreign_keys and making them a list. From official documentation on Relationship Configuration: Handling Multiple Join Paths

Changed in version 0.8: relationship() can resolve ambiguity between foreign key targets on the basis of the foreign_keys argument alone; the primaryjoin argument is no longer needed in this situation.

Self-contained code below works with sqlalchemy>=0.9:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(u'sqlite:///:memory:', echo=True)
session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()

#The business case here is that a company can be a stakeholder in another company.
class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)

class Stakeholder(Base):
    __tablename__ = 'stakeholder'
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    company = relationship("Company", foreign_keys=[company_id])
    stakeholder = relationship("Company", foreign_keys=[stakeholder_id])

Base.metadata.create_all(engine)

# simple query test
q1 = session.query(Company).all()
q2 = session.query(Stakeholder).all()
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
van
  • 74,297
  • 13
  • 168
  • 171
  • 15
    After doing that, am getting the error `sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'company' and 'stakeholder'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.` – lukik Mar 12 '14 at 17:26
  • what version of sqlalchemy do you have? – van Mar 12 '14 at 19:28
  • Got `SQLAlchemy==0.9.1` – lukik Mar 13 '14 at 04:09
  • Ok. This has worked. Question though, would it make sense to put a backref on the Company table like this `stakeholder = relationship("Stakeholder", backref="company")`? or that would be redundant? – lukik Mar 13 '14 at 04:34
  • 2
    It depends if you need it or not. Do you need to navigate from `Stakeholder` to the `Company`? In any case be careful because you cannot put the same name for `backref` for both relationships, so in case you do, i would put something like `company = relationship("Company", foreign_keys=[company_id], backref="holders")` and `stakeholder = relationship("Company", foreign_keys=[stakeholder_id], backref="holdings")`. – van Mar 13 '14 at 08:00
  • Another question: if your `Stakeholder` table does not contain anything else (like percentage owned, since when etc), but pure relationship information, why don't you use pure [Many-to-Many](http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#many-to-many) relationship setup? – van Mar 13 '14 at 08:03
  • Thank for the suggestion. As for the stakeholder table, it does contain other attributes so the many to many relationship wouldn't do for now – lukik Mar 13 '14 at 19:09
  • How to use this with mixins? – Petrus Theron Oct 28 '16 at 14:40
  • `NameError: name 'thing_id' is not defined.` With latest sqla stable. :-( – Gringo Suave Jul 28 '17 at 19:04
  • 3
    Why sqlalchemy use foreign_keys argument as a list if we always pass only one element list? is there some situation when we use foreign keys as two-element list? – Matija Lukic May 23 '19 at 19:28
  • Backref was discussed but what about 'back_populates'? – Rhdr Feb 17 '20 at 11:13
  • Here is a more clear explanation if the above answer does not work: https://stackoverflow.com/a/44434708/7335848 – elano7 Dec 31 '22 at 11:24
  • @MatijaLukic you can create composite foreign key, so this is when you'd put multiple columns inside this list. – winwin Aug 13 '23 at 09:17
11

The latest documentation:

The form of foreign_keys= in the documentation produces a NameError, not sure how it is expected to work when the class hasn't been created yet. With some hacking I was able to succeed with this:

company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
company = relationship("Company", foreign_keys='Stakeholder.company_id')

stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
stakeholder = relationship("Company",
                            foreign_keys='Stakeholder.stakeholder_id')

In other words:

… foreign_keys='CurrentClass.thing_id')
Gringo Suave
  • 29,931
  • 6
  • 88
  • 75