SQL Alchemy AmbiguousForeignKeysError but foreign_keys are specified

Question

I’m trying to figure out how relationships in sqlalchemy works, here are my models:

class Department(Base):
    title = Column(String(50))

    employees = relationship('Employee', back_populates='department')
    head = relationship(
        'Employee', back_populates='head_of_department', uselist=False)


class Employee(Base):
    """Сотрудник. """
    first_name = Column(String(30))
    last_name = Column(String(40))
    first_day = Column(Date)
    last_day = Column(Date)

    department_id = Column(Integer, ForeignKey('departments.id'))
    department = relationship('Department', back_populates='employees', foreign_keys=[department_id])

    head_of_department_id = Column(Integer, ForeignKey('departments.id'))
    head_of_department = relationship('Department', back_populates='head', foreign_keys=[head_of_department_id])

But, when I’m trying to add department like d = Department(title=’Sales’), I’m getting and error:

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

But I have specify foreign key parameter, what is wrong

Answer 1

The problem seems to be that your head_of_department_id and department_id are both configured to be a foreign key to departments.id.

Your head_of_department_id should not be a foreign key. You don't have a "heads of department" table for it to link to. Here it is trying to link to a record that specifies a department. That record does contain information about the department head, but you would link that through a join on the department ID (which is correctly configured to be a foreign key).

It's possible that you do want department.head to be a foreign key back to employees, if details of heads of department are stored in the employees table.

Answer 2

I have to add foreign_keys in PARENT model:

class Department(Base): title = Column(String(50))

employees = relationship('Employee', back_populates='department',
                         foreign_keys='Employee.department_id')
head = relationship('Employee', back_populates='head_of_department',
                    foreign_keys='Employee.head_of_department_id',
                    uselist=False)