SQLAlchemy复合外键反向填充父属性报错，求解决方法

你遇到的核心问题是SQLAlchemy无法自动识别单表继承类与子表的复合外键关联，主要有两个潜在原因：单表继承缺少鉴别器配置，或者关联关系未明确指定连接条件。以下是针对性的解决方案：

方案一：修复单表继承配置（允许修改原有表结构） #

因为ParentEntity与PreviouslyExistingEntity共用同一张表，属于SQLAlchemy的单表继承场景，但你没有配置鉴别器列，导致ORM无法区分父类与子类的映射关系，进而无法关联外键约束。

修改代码如下：

from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column

class PreviouslyExistingEntity(BaseEntity):
    __tablename__ = "previously_existing_table"

    # 添加鉴别器列，用于区分父类/子类实例
    entity_type: Mapped[str] = mapped_column(String(50), nullable=False, default="base")
    one_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    another_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    category_name: Mapped[str] = mapped_column(String(100), nullable=False)
    subcategory_name: Mapped[str] = mapped_column(String(100), nullable=False)

    # 主键已保证唯一性，可删除多余的UniqueConstraint
    __table_args__ = ()

    __mapper_args__ = {
        "polymorphic_on": entity_type,  # 指定鉴别器列
        "polymorphic_identity": "base" # 父类的鉴别值
    }

class ParentEntity(PreviouslyExistingEntity):
    """Extends PreviouslyExistingEntity with new relationships."""

    __tablename__ = "previously_existing_table"
    __table_args__ = ({"extend_existing": True},)

    __mapper_args__ = {
        "polymorphic_identity": "extended" # 子类的鉴别值
    }

    children: Mapped[list["ChildEntity"]] = relationship(
        "ChildEntity",
        lazy="selectin",
        back_populates="parent",
    )

方案二：显式指定关联条件（无需修改原有表结构） #

如果无法修改previously_existing_table的结构，直接在relationship中明确声明连接条件和外键列，强制ORM识别关联关系：

from sqlalchemy import and_

class ParentEntity(PreviouslyExistingEntity):
    """Extends PreviouslyExistingEntity with new relationships."""

    __tablename__ = "previously_existing_table"
    __table_args__ = ({"extend_existing": True},)

    children: Mapped[list["ChildEntity"]] = relationship(
        "ChildEntity",
        lazy="selectin",
        back_populates="parent",
        # 显式定义复合主键的连接条件
        primaryjoin=and_(
            ParentEntity.one_id == ChildEntity.one_id,
            ParentEntity.another_id == ChildEntity.another_id
        ),
        # 指定子表的外键列
        foreign_keys="[ChildEntity.one_id, ChildEntity.another_id]"
    )

class ChildEntity(BaseEntity):
    __tablename__ = "child_table"

    __table_args__ = (
        ForeignKeyConstraint(
            ["one_id", "another_id"],
            ["previously_existing_table.one_id", "previously_existing_table.another_id"],
        ),
    )

    child_id: Mapped[str] = mapped_column(String(100), primary_key=True)
    one_id: Mapped[int] = mapped_column(Integer)
    another_id: Mapped[int] = mapped_column(Integer)
    name: Mapped[str] = mapped_column(String(100))
    parent: Mapped["ParentEntity"] = relationship(
        "ParentEntity",
        lazy="selectin",
        back_populates="children",
        # 反向关联同样显式指定连接条件
        primaryjoin=and_(
            ChildEntity.one_id == ParentEntity.one_id,
            ChildEntity.another_id == ParentEntity.another_id
        ),
        foreign_keys="[ChildEntity.one_id, ChildEntity.another_id]"
    )

额外说明 #

原代码中PreviouslyExistingEntity的UniqueConstraint是多余的——主键本身已经保证了one_id和another_id的唯一性，可直接删除。

内容的提问来源于stack exchange，提问作者Iván Pazmiño