CODEWITHSUNDEEP
pythonpostgresqlormsqlalchemy
Sylwester Kardziejonek
Sylwester Kardziejonek

Reputation: 592

SQLAlchemy One-To-One and One-To-Many at the same time (AmbiguousForeignKeysError)

I'm working with SQLAlchemy and I try to achieve one-to-one and one-to-many relationships on the same parent class. This is for the simplicity of keeping track of the main child entity.

Unfortunately I'm getting an error:

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.contact - 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.

Am I doing something wrong or it is not possible?

Here is a code example:

class Customer(Base):

    __tablename__ = 'customer'

    id = Column(Integer, primary_key=True)
    contact_id = Column(Integer, ForeignKey('contact.id'))
    address_id = Column(Integer, ForeignKey('address.id'))

    contact = relationship('Contact', backref=backref("contact", uselist=False))
    address = relationship('Address', backref=backref("address", uselist=False))

    contact_list = relationship('Contact')
    address_list = relationship('Address')


class Contact(Base):

    __tablename__ = 'contact'

    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, ForeignKey(
        'customer.id',
        use_alter=True, name='fk_contact_customer_id_customer',
        onupdate='CASCADE', ondelete='SET NULL'
    ))
    first_name = Column(String(32))
    last_name = Column(String(32))


class Address(Base):

    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, ForeignKey(
        'customer.id',
        use_alter=True, name='fk_address_customer_id_customer',
        onupdate='CASCADE', ondelete='SET NULL'
    ))
    label = Column(String(32))

Thanks

Upvotes: 2

Views: 1023

Answers (1)

Sylwester Kardziejonek
Sylwester Kardziejonek

Reputation: 592

Apparently the solution was later in the documentation: SQLAlchemy does not know which foreign key to use, so you have to specify those as Column objects in relationship(foreign_keys=[]) like so:

class Contact(Base):
    # ...
    customer_id = Column(Integer, ForeignKey(
        'customer.id',
        use_alter=True, name='fk_contact_customer_id_customer',
        onupdate='CASCADE', ondelete='SET NULL'
    ))
    # ...


class Customer(Base):
    # ...
    contact_id = Column(Integer, ForeignKey('contact.id'))
    #...
    contact = relationship('Contact', uselist=False, foreign_keys=[contact_id])
    contact_list = relationship('Contact', foreign_keys=[Contact.customer_id])
    #...

Upvotes: 3

Related Questions