如何在SQLModel/SQLAlchemy中设置带级联删除的双外键主键

问题核心是Group2User类缺少table=True参数，导致SQLModel没有将其识别为数据库表，因此外键约束和级联删除规则都没有被生成。以下是修正后的完整代码：

import enum
import uuid
from typing import Optional, List
from sqlmodel import SQLModel, Field, Relationship
from sqlalchemy import Column, String, UUID

class User(SQLModel, table=True):
    user_id: str = Field(primary_key=True, nullable=False)
    # 关联用户所属的单个组（原有的一对一关联保留）
    user_group: Optional["Group"] = Relationship(
        sa_relationship_kwargs={"uselist": False, "cascade": "save-update,merge,expunge,delete,delete-orphan"}
    )
    # 反向关联用户参与的所有组权限记录
    group_associations: List["Group2User"] = Relationship(back_populates="user", cascade="all, delete")

class Group(SQLModel, table=True):
    group_id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True, index=True, nullable=False)
    user_id: Optional[str] = Field(sa_column=Column(String, ForeignKey("user.user_id", ondelete="CASCADE")))
    # 反向关联组的所有权限记录
    user_list: List["Group2User"] = Relationship(back_populates="group", cascade="all, delete")

class GroupRights(enum.Enum):
    READ = "read"
    WRITE = "write"
    ADMIN = "admin"

# 关键：添加table=True，让SQLModel生成该表
class Group2User(SQLModel, table=True):
    user_id: str = Field(
        sa_column=Column(String, ForeignKey("user.user_id", ondelete="CASCADE"), nullable=False, primary_key=True)
    )
    group_id: uuid.UUID = Field(
        sa_column=Column(UUID, ForeignKey("group.group_id", ondelete="CASCADE"), primary_key=True, nullable=False)
    )
    rights: GroupRights = Field(default=GroupRights.READ)
    
    # 双向关联User和Group
    user: User = Relationship(back_populates="group_associations")
    group: Group = Relationship(back_populates="user_list")

关键修正点说明 #

• 添加table=True到Group2User：这是核心，没有这个参数，SQLModel只会把类当作数据模型，不会生成对应的数据库表结构，外键约束自然无法生效。
• 补全缺失的导入：添加import uuid和from sqlalchemy import Column, String, UUID，避免运行时报错。
• 配置双向关联：在User、Group和Group2User之间添加back_populates关联，确保ORM层面的级联操作（如删除用户时自动删除关联权限记录）正常工作，同时配合数据库层面的ondelete="CASCADE"实现双重保障。

修正后的表结构 #

生成的group2user表会包含正确的外键和级联删除规则：

CREATE TABLE group2user (
    user_id VARCHAR NOT NULL,
    group_id UUID NOT NULL,
    rights grouprights NOT NULL,
    PRIMARY KEY (user_id, group_id),
    FOREIGN KEY(user_id) REFERENCES "user" (user_id) ON DELETE CASCADE,
    FOREIGN KEY(group_id) REFERENCES "group" (group_id) ON DELETE CASCADE
)

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