SQLAlchemy子类自引用多对多关系添加关联时报错排查

你的错误出在多对多关联的relationship配置不完整。由于User和Group都是Subject的子类，SQLAlchemy无法自动推断关联表users_groups中两个外键分别对应哪一方，导致添加群组时只插入了group_id，缺失user_id，触发非空约束错误。

修改User和Group中的relationship定义，明确指定关联的主键连接条件：

class User(Subject):
    __mapper_args__ = {'polymorphic_identity': AuthorizableType.USER}

    user_name = Column(String(50))
    groups = relationship('Group', back_populates='users',
        secondary='users_groups',
        # 明确User.id对应users_groups.user_id
        primaryjoin='User.id == UserGroup.user_id',
        # 明确Group.id对应users_groups.group_id
        secondaryjoin='Group.id == UserGroup.group_id'
    )

class Group(Subject):
    __mapper_args__ = {'polymorphic_identity': AuthorizableType.GROUP}

    group_name = Column(String(50))
    users = relationship('User', back_populates='groups',
        secondary='users_groups',
        # 明确Group.id对应users_groups.group_id
        primaryjoin='Group.id == UserGroup.group_id',
        # 明确User.id对应users_groups.user_id
        secondaryjoin='User.id == UserGroup.user_id'
    )

或者，也可以通过foreign_keys参数同时指定两个关联外键（效果一致）：

class User(Subject):
    __mapper_args__ = {'polymorphic_identity': AuthorizableType.USER}

    user_name = Column(String(50))
    groups = relationship('Group', back_populates='users',
        secondary='users_groups',
        foreign_keys='[UserGroup.user_id, UserGroup.group_id]'
    )

class Group(Subject):
    __mapper_args__ = {'polymorphic_identity': AuthorizableType.GROUP}

    group_name = Column(String(50))
    users = relationship('User', back_populates='groups',
        secondary='users_groups',
        foreign_keys='[UserGroup.group_id, UserGroup.user_id]'
    )

修改后重新运行代码，user1.groups.append()会正确生成包含user_id和group_id的INSERT语句，不再触发完整性错误。

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