sqlalchemy.exc.AmbiguousForeignKeysError after Inheritance

Question

I'm using sqlacodegen for reflecting a bunch of tables from my database. And i'm getting the following error:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'Employee' and 'Sales'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Here's a simplified version of my tables. I read in the documentation that I should use the foreign_keys parameter to resolve ambiguity between foreign key targets. Although, I think this problem is because of the inheritance. Could someone help me understand what is going on.

# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Employee(Base):
    __tablename__ = 'Employee'

    EmployeeId = Column(Integer, primary_key=True)


class Sales(Employee):
    __tablename__ = 'Sales'

    EmployeeID = Column(ForeignKey('Employee.EmployeeId'), primary_key=True)
    OldemployeeID = Column(ForeignKey('Employee.EmployeeId'))
    employee = relationship('Employee', foreign_keys=[EmployeeID])
    old_employee = relationship("Employee", foreign_keys=[OldemployeeID])

Answer 1

Just use backref and use Integer on both EmployeeID and OldemployeeID. Otherwise you will get an another error.

class Sales(Employee):
    __tablename__ = 'Sales'

    EmployeeID = Column(Integer, ForeignKey('Employee.EmployeeId'), primary_key=True)
    OldemployeeID = Column(Integer, ForeignKey('Employee.EmployeeId'))
    employee = relationship('Employee', foreign_keys=[EmployeeID], backref='Employee')
    old_employee = relationship("Employee", foreign_keys=[OldemployeeID], backref='Employee')

Answer 2

When your tables have multiple possible paths to inherit between them (Sales.EmployeeID or Sales.OldEmployeeID), SqlAlchemy doesn't know which one to use and you'll need to tell it the path explicitly, by using inherit_condition. For instance to inherit by EmployeeID:

class Sales(Employee):
    ...
    __mapper_args__ = { "inherit_condition": EmployeeID == Employee.EmployeeId }

For the sake of example, you could also inherit by OldEmployeeID, by entering OldEmployeeID == Employee.EmployeeId - this would mean that both your Sales primary key and the Employee primary key are allowed to be different.