SQLAlchemy复合外键双向关联对象多对多关系报错排查

你的代码触发NoForeignKeysError是两处配置错误导致的：

• 重复且错误定义了指向Project的外键：你已经在Subscription的__table_args__中通过ForeignKeyConstraint定义了project_id、repo两列联合关联Project的复合主键，却又在这两个列的Column定义中单独加了ForeignKey约束。单独的外键声明不满足复合主键关联要求，还会打乱SQLAlchemy的外键自动识别逻辑。
• foreign_keys参数传值完全不符合要求：relationship的foreign_keys参数需要传入当前关联表（即Subscription表）自身持有的外键列，你传入了User、Project两张表的列，甚至把不属于Project关联的User.user_id也包含在内，SQLAlchemy无法识别到有效关联外键，自然抛出异常。

修正后可运行代码 #

from sqlalchemy import Column, String, ForeignKey, ForeignKeyConstraint
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "user"

    user_id = Column(String, primary_key=True)
    subscriptions = relationship(
        "Subscription", back_populates="user"
    )

class Project(Base):
    __tablename__ = 'project'

    project_id = Column(String, primary_key=True)
    repo = Column(String, primary_key=True)
    subscribers = relationship(
        "Subscription", back_populates="project"
    )

class Subscription(Base):
    __tablename__ = "subscription"
    __table_args__ = (
        ForeignKeyConstraint(
            ['project_id', 'repo'],
            ['project.project_id', 'project.repo'],
        ),
    )
    user_id = Column(ForeignKey("user.user_id"), primary_key=True)
    # 删掉重复的单列ForeignKey定义，仅保留联合外键约束即可
    project_id = Column(String, primary_key=True)
    repo = Column(String, primary_key=True)
    extra = Column(String(50))
    user = relationship("User", back_populates="subscriptions")
    # 外键配置正确时无需手动指定foreign_keys，SQLAlchemy可自动识别关联关系
    project = relationship("Project", back_populates="subscribers")

补充说明：如果遇到复杂关联场景必须手动指定foreign_keys，也需要传入当前表的列对象，正确写法为foreign_keys="[Subscription.project_id, Subscription.repo]"，不可传入关联目标表的列。

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