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])

Be sure to specify type (not just Column(Foreign..., but Column(Integer,Foreign....). I've never seen it done without it, not sure if that's the issue here though. I've never done table inheritance. — ApolloFortyNine, Oct 18 '16 at 17:41
What type of inheritance do you want to do? It appears you haven't configured your classes for inheritance. — univerio, Oct 18 '16 at 18:38
I didn't actually do the inheritance myself. This is how some of my tables turn out when i run `sqlacodegen` on my database. — BoreBoar, Oct 18 '16 at 19:07
Well, code generation tools are not perfect. If you don't want inheritance then don't use inheritance. — univerio, Oct 18 '16 at 20:36

score 1 · Answer 1 · answered Nov 22 '18 at 16:52

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.

score 0 · Answer 2 · answered Oct 19 '16 at 10:34

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')

I'm still getting the same `AmbiguousForeignKeysError`. It occurs when I run the same file where I have defined these two classes. — BoreBoar, Oct 19 '16 at 10:49

sqlalchemy.exc.AmbiguousForeignKeysError after Inheritance

2 Answers2