记一次从 SQLAlchemy UOW 怀疑人生，到发现 SQLite 外键根本没开的荒诞经历。
症状
事情是这样的。我的项目 CommandLab 有一个函数上传接口，用户可以上传 .mcd 命令脚本文件到私有库。第一次上传没问题，但是当用户更新同一个函数的时候，后端直接炸了：
sqlite3.IntegrityError: UNIQUE constraint failed: metadata.function_id, metadata.key
[SQL: INSERT INTO metadata (function_id, "key", value) VALUES (?, ?, ?)]
[parameters: (8, 'name', 'A function name')]
metadata 表对 (function_id, key)有唯一约束。报错说 function_id=8, key='name' 已经存在了，你不能再插一行。
看起来很简单对吧？旧的 metadata 没删干净呗。
但事情远没有这么简单。
第一轮：怀疑 SQLAlchemy 的 Unit of Work
我的第一反应是：SQLAlchemy 的 UOW（Unit of Work）在 flush/commit 的时候执行 SQL 的顺序有问题。 也许它先 INSERT 了新的 metadata，然后才去 DELETE 旧的？
于是我尝试了：
# 方案1：用 raw SQL DELETE 强制先删
await session.execute(delete(FunctionMetadata).where(...))
await session.flush()
# 然后再 INSERT 新的
还是炸。
# 方案2：用 ORM 集合替换
existing_private.metadata_items.clear()
await session.flush()
existing_private.metadata_items = [FunctionMetadata(...), ...]
还是炸。
# 方案3：完全不删不增，原地修改 value
current_meta_map = {m.key: m for m in existing_private.metadata_items}
for key, value in metadata.items():
if key in current_meta_map:
current_meta_map[key].value = value # 只改 value
else:
session.add(FunctionMetadata(...))
依！然！炸！
我开始怀疑人生。明明是 In-Place 修改，压根没有 DELETE 和 INSERT，为什么还是 UNIQUE constraint failed？
第二轮：发现 session.refresh() 在异步环境下的诡异行为
在第三个方案里，我用了 await session.refresh(existing_private, ["metadata_items"]) 来加载关联集合。
但在 async + aiosqlite 环境下，refresh之后访问 existing_private.metadata_items，它没有报错，也没有触发 lazy load，而是静悄悄地返回了一个空列表。
所以 current_meta_map 是空的 → 所有 key 都走了 session.add(新对象) → 和数据库已有的行撞车 → 炸
我改成了显式 select() 查询：
stmt = select(FunctionMetadata).where(FunctionMetadata.function_id == existing_private.id)
existing_metas = (await session.execute(stmt)).scalars().all()
本地测试通过了！我信心满满地部署到生产环境。
结果还是炸。
第三轮：把生产数据库拷下来
这时候我终于做了一件早就该做的事——把生产环境的 commandlab.db 拷到本地来看。
-- 查 function_id=8 的 metadata
SELECT * FROM metadata WHERE function_id = 8;
-- 结果：5 行，name/note/tags/uuid/version，每个 key 只有一行
-- 数据很干净啊？
-- 那 function_id=8 的函数本体呢？
SELECT * FROM functions WHERE id = 8;
-- 结果：
-- (空)
等等，什么？？
SELECT MAX(id) FROM functions;
-- 7
SELECT id, function_id, key FROM metadata
WHERE function_id NOT IN (SELECT id FROM functions);
-- 34 8 name
-- 37 8 note
-- 36 8 tags
-- 38 8 uuid
-- 35 8 version
function_id=8 的函数已经被删了，但它的 metadata 还活着。
这就是所谓的"孤儿数据"。当新函数被创建时，SQLite 重用了 id=8（因为没用 AUTOINCREMENT），然后插入 metadata 的时候就和这些孤儿撞上了。
但是，我明明写了 CASCADE 啊？？
class FunctionMetadata(Base):
function_id = mapped_column(
Integer, ForeignKey("functions.id", ondelete="CASCADE"), nullable=False
)
白纸黑字，ondelete="CASCADE"。删除 function 的时候，metadata 应该自动跟着删啊？
我又去看了 session.py：
async def init_database():
_engine = create_async_engine(f"sqlite+aiosqlite:///{DB_PATH}")
async with _engine.begin() as conn:
await conn.execute(text("PRAGMA foreign_keys = ON")) # ← 你看，写了！
await conn.run_sync(Base.metadata.create_all)
写了 PRAGMA foreign_keys = ON 啊，没毛病啊？
有毛病。
SQLite 的惊天大坑：PRAGMA 是连接级别的
在 SQLite 中，PRAGMA foreign_keys = ON 只对当前连接生效。
上面的代码在 init_database() 里用 engine.begin() 拿了一个连接，设置了 PRAGMA，创建了表，然后这个连接就还回连接池了。
之后所有 API 请求拿到的都是新的连接，它们的 foreign_keys 依然是 OFF。
也就是说：
从项目第一天起，你的 ondelete="CASCADE" 就从来没有生效过。
每次删除 function 的时候，metadata 行都安安静静地留在那里，变成一颗颗定时炸弹，等着某天 SQLite 重用了那个 ID，然后……炸
修复
修复本身反而是最简单的部分：
from sqlalchemy import event
@event.listens_for(_engine.sync_engine, "connect")
def _set_sqlite_pragma(dbapi_conn, connection_record):
cursor = dbapi_conn.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
用 SQLAlchemy 的 event.listens_for("connect") 钩子，在每个新连接建立时都执行 PRAGMA。这是 SQLAlchemy 官方文档 推荐的做法。
然后清理孤儿数据：
DELETE FROM metadata WHERE function_id NOT IN (SELECT id FROM functions);
-- 5 rows deleted
搞定。
回顾
| 我以为的问题 | 实际的问题 |
|---|---|
| SQLAlchemy UOW 执行顺序不对 | 和 UOW 毫无关系 |
session.refresh() 异步环境有 bug |
确实有坑，但不是根因 |
| ORM 集合操作不可靠 | 集合本身没问题 |
| 需要升级 ORM 版本 | 不需要 |
| SQLite 外键没开 | 就是这个 |
一行 PRAGMA，五条孤儿，一整晚的 debug。
教训
- SQLite 的外键约束默认是关闭的。 这不是 bug，这是"feature"。每个新连接都需要手动开启。
PRAGMA是连接级别的，不是数据库级别的。 在连接池环境下，你必须用 event listener 来确保每个连接都正确配置。- 当 ORM 层面的所有尝试都失败时，去看看数据库本身。 我在 SQLAlchemy 的 UOW 逻辑上浪费了大量时间，而真正的答案一条
SELECT就能发现。
下次再遇到 SQLite + ondelete="CASCADE" 不生效的时候，先查 PRAGMA foreign_keys。
别问我怎么知道的。
:wq