SQLite与SQLAlchemy下复合主键多外键表级联删除报错

该错误与SQLite本身的级联删除能力无关，复合主键作为外键配置级联删除在SQLite中是完全支持的，问题出在两层配置缺失：

• SQLAlchemy的automap反射默认不会自动识别数据库表定义中配置的ON DELETE CASCADE规则，默认给关联关系配置的行为是：删除父表记录时，尝试将关联子表记录的对应外键字段置为NULL，避免触发数据库外键约束错误。但当前场景中IndividualSample.id_execution是复合主键的组成字段，主键不允许为NULL，因此直接抛出断言错误。
• SQLite默认关闭外键约束支持，即使表结构定义了外键级联规则，未手动开启配置的话，数据库侧不会实际执行级联删除动作。

根据实现偏好二选一即可：

方案1：依赖数据库侧级联规则，性能更优 #

该方案将级联删除动作完全交给SQLite引擎执行，ORM不会额外加载子记录生成多余DELETE语句，适合数据量较大的场景。

1. 首先给SQLite引擎添加连接事件，强制开启外键约束支持：

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def enable_sqlite_fk(dbapi_conn, _):
    cursor = dbapi_conn.cursor()
    cursor.execute("PRAGMA foreign_keys = ON")
    cursor.close()

2. 修改automap配置，告知ORM当前外键配置了数据库侧级联删除，不要主动修改子记录外键值：

Base = automap_base()
Base.prepare(
    autoload_with=self.engine,
    reflect=True,
    relationship_kwargs={
        "passive_deletes": True
    }
)

self.ExecutionVCE = Base.classes.ExecutionVCE
self.IndividualSample = Base.classes.IndividualSample

配置passive_deletes=True后，删除ExecutionVCE记录时ORM不会主动加载关联的IndividualSample记录，也不会尝试修改其外键字段，直接发送单条DELETE语句给SQLite，由数据库自动完成关联子记录的删除。

方案2：ORM层实现级联删除，无数据库依赖 #

该方案由SQLAlchemy ORM层控制级联逻辑，不依赖数据库外键约束，兼容性更强。在反射完成后手动给两表关联关系添加级联删除配置即可：

from sqlalchemy.orm import relationship

Base = automap_base()
Base.prepare(self.engine, reflect=True)

self.ExecutionVCE = Base.classes.ExecutionVCE
self.IndividualSample = Base.classes.IndividualSample

# 手动补全双向关系与级联配置
self.ExecutionVCE.individual_samples = relationship(
    self.IndividualSample,
    backref="execution",
    cascade="all, delete-orphan"
)

配置后删除ExecutionVCE记录时，ORM会先查询所有关联的IndividualSample记录并逐个删除，再删除父表记录，全程不需要数据库侧开启外键级联。

如果执行删除操作前，session中已经加载了关联的IndividualSample对象，即使配置了passive_deletes=True，ORM仍会处理这些已缓存的对象。这种场景下要么同步配置ORM层的级联删除规则，要么在删除父记录前调用session.expire_all()清空缓存的对象状态，避免触发置空主键的逻辑。

内容的提问来源于stack exchange，提问作者Juan Sebastián Ramírez Artiles