SQLite中SQLAlchemy多对多关联表ON DELETE CASCADE失效原因咨询

嘿，这个坑我之前踩过！你遇到的问题核心原因是SQLite默认禁用了外键约束——哪怕你在表定义里明确写了ondelete="CASCADE"，如果没开启SQLite的外键支持，这些约束规则根本不会生效。除此之外，还有个小细节是事务提交的问题，也得注意。

具体要补充的配置 #

1. 创建引擎时开启外键支持 #

在创建engine的时候，必须添加connect_args={"foreign_keys": True}参数，让SQLite启用外键约束。如果是内存数据库，建议顺便加上check_same_thread=False避免线程问题：

engine = sa.create_engine("sqlite:///:memory:", echo=True, connect_args={"foreign_keys": True, "check_same_thread": False})

这个参数的作用是让SQLAlchemy在每次建立连接时自动执行PRAGMA foreign_keys = ON;，这是SQLite启用外键约束的必要操作。

2. 确保操作在事务中提交 #

你的删除操作需要提交事务才能生效，SQLite的内存数据库对未提交的修改不会持久化，外键的CASCADE操作也不会触发。比如删除角色的代码要加上提交步骤：

# 删除ID为1的Admin角色
conn.execute(roles.delete().where(roles.c.id == 1))
# 一定要提交事务！
conn.commit()

修正后的完整代码示例 #

import sqlalchemy as sa
# 关键：创建引擎时开启外键支持
engine = sa.create_engine("sqlite:///:memory:", echo=True, connect_args={"foreign_keys": True, "check_same_thread": False})
metadata = sa.MetaData()
userroles = sa.Table(
    "userroles", metadata,
    sa.Column("user_id", sa.Integer, sa.ForeignKey("users.id", ondelete="CASCADE")),
    sa.Column("role_id", sa.Integer, sa.ForeignKey("roles.id", ondelete="CASCADE")),
)
users = sa.Table(
    "users", metadata,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("name", sa.String),
)
roles = sa.Table(
    "roles", metadata,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("name", sa.String),
)
metadata.create_all(engine)
conn = engine.connect()
# 插入数据后先提交事务
conn.execute(users.insert().values(name="Joe"))
conn.execute(roles.insert().values(name="Admin"))
conn.execute(roles.insert().values(name="User"))
conn.execute(userroles.insert().values(user_id=1, role_id=1))
conn.commit()

# 删除角色并提交
conn.execute(roles.delete().where(roles.c.id == 1))
conn.commit()

# 验证关联行是否被删除
result = conn.execute(sa.select(userroles)).fetchall()
print("剩余关联行：", result)  # 这里应该输出空列表，说明CASCADE生效了

额外提醒 #

如果之后你切换到SQLAlchemy ORM（比如用declarative_base定义模型），同样需要在引擎中添加这个外键开启参数，原理是完全一样的。SQLite的外键约束必须手动开启，这是它和MySQL、PostgreSQL这类数据库不一样的地方，很容易被忽略。

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