使用sqlalchemy.orm关联复合主键时遇AmbiguousForeignKeysError错误

你的问题核心在于：Customer与Address之间存在多组外键关联（Customer的两个地址字段指向Address，Address的两个字段指向Customer复合主键），SQLAlchemy无法自动推断Address.customers_using_this_adress这个反向关联的具体逻辑，即便指定了foreign_keys，仍需显式定义primaryjoin来明确复合主键的匹配规则。

修改后的可运行代码 #

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customer"
    id = mapped_column(String, primary_key=True)
    country = mapped_column(String, primary_key=True)

    billing_address_id = mapped_column(Integer, ForeignKey("address.idx"))
    shipping_address_id = mapped_column(Integer, ForeignKey("address.idx"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])


class Address(Base):
    __tablename__ = "address"
    idx = mapped_column(Integer, primary_key=True)
    address = mapped_column(String)
    customer_id = mapped_column(ForeignKey("customer.id"))
    customer_country = mapped_column(ForeignKey("customer.country"))
    
    # 显式指定复合主键的关联条件
    customers_using_this_adress = relationship(
        "Customer",
        foreign_keys=[customer_id, customer_country],
        primaryjoin="and_(Address.customer_id == Customer.id, Address.customer_country == Customer.country)"
    )

# 现在可正常创建实例
c = Customer(id="A", country="B")

报错原因说明 #

你之前的代码中，Customer有两个外键（billing_address_id、shipping_address_id）指向Address.idx，同时Address有两个外键指向Customer的复合主键。这种双向多外键的场景下，SQLAlchemy无法自动判断Address.customers_using_this_adress应该基于哪一组外键关联，必须通过primaryjoin参数写出完整的复合主键匹配条件，明确告诉ORM如何关联两张表。

原报错信息 #

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

内容的提问来源于stack exchange，提问作者SultanOrazbayev