SQLAlchemy子类间一对多关系配置异常问题求助

你的代码存在两个关键错误，直接导致了Client的ID被覆盖：

1. BusinessUnitModel中多余的外键字段 #

作为一对多关系里的"一"方，BusinessUnitModel不需要定义client_id外键字段——这个关联字段只应该存在于"多"方（ClientModel）的business_unit_id中。多余的client_id会打乱SQLAlchemy的映射逻辑。

2. relationship的foreign_keys参数配置错误 #

在BusinessUnitModel的clients关系中，你错误地将foreign_keys指向了ClientModel.id，这会让SQLAlchemy误以为要关联Client的主键，而实际上应该关联的是ClientModel.business_unit_id这个外键字段。

修正后的代码 #

class CompanyModel(db.Model):
    __tablename__ = "companies"
    company_id = db.Column(db.String, primary_key=True)
    type = db.Column(db.Enum(CompanyType))

    __mapper_args__ = {
        "polymorphic_on": type,
        "polymorphic_identity": CompanyType.COMPANY,
    }
    
    def __init__(...):
        ...

class BusinessUnitModel(CompanyModel):
    __tablename__ = "business_units"
    id = db.Column(db.String, db.ForeignKey("companies.company_id"), primary_key=True)

    # 修正一对多关系：移除多余字段，指定正确的外键关联
    clients = db.relationship(
        "ClientModel", back_populates="business_unit", foreign_keys="ClientModel.business_unit_id"
    )
    
    __mapper_args__ = {
        "polymorphic_identity": CompanyType.BUSINESS_UNIT,
    }
    
    def __init__(...):
        ...

class ClientModel(CompanyModel):
    __tablename__ = "clients"
    id = db.Column(db.String, db.ForeignKey("companies.company_id"), primary_key=True)

    # 多对一关系保持现有配置即可
    business_unit_id = db.Column(db.String, db.ForeignKey("business_units.id"))
    business_unit = db.relationship(
        "BusinessUnitModel", back_populates="clients", foreign_keys=business_unit_id
    )
    
    __mapper_args__ = {
        "polymorphic_identity": CompanyType.CLIENT,
    }
    
    def __init__(...):
        ...

修正说明 #

• 删除BusinessUnitModel中的client_id字段，消除映射冲突。
• 将BusinessUnitModel.clients关系的foreign_keys改为ClientModel.business_unit_id，让SQLAlchemy正确识别关联字段。此时向BusinessUnit的clients列表添加Client时，只会更新Client的business_unit_id，不会修改Client的主键ID。

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