SQLite 延迟外键：COMMIT 已经报错，事务为什么还没有结束
导入关联数据时，子记录先到，父记录稍后才写入。延迟外键允许这类暂时不完整的状态存在于事务内部，但提交时仍必须满足约束。容易漏掉的是恢复动作：COMMIT 因延迟外键失败之后，事务可能仍然开着，已经执行的插入也还在其中。
把检查推迟到提交边界
本例为外键明确声明 DEFERRABLE INITIALLY DEFERRED，并在连接建立后启用检查。父表主键是合法引用目标，子引用列禁止空值。isolation_level=None 让示例用显式 SQL 控制事务起止，避免把驱动自动开始事务的时机混进来。
保存为 demo.py，运行 python demo.py。内存库从空表开始，先插入指向父编号十的子记录，再尝试提交。此时没有父记录，提交必然失败；代码检查扩展错误名称和 in_transaction，不把捕获到异常直接等同于已经回滚。
AI概念示意图：缺失关联挡住提交出口，事务仍可保留，补齐关系后再次通过检查。
import sqlite3
db = sqlite3.connect(":memory:", isolation_level=None)
try:
db.execute("PRAGMA foreign_keys=ON")
assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
db.executescript("""
CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER NOT NULL REFERENCES parent(id)
DEFERRABLE INITIALLY DEFERRED
);
""")
def rejected_commit():
try:
db.execute("COMMIT")
except sqlite3.IntegrityError as error:
assert error.sqlite_errorname == "SQLITE_CONSTRAINT_FOREIGNKEY"
else:
raise AssertionError("invalid transaction committed")
assert db.in_transaction
db.execute("BEGIN")
db.execute("INSERT INTO child VALUES(1, 10)")
rejected_commit()
assert db.execute("SELECT id FROM child").fetchall() == [(1,)]
print("failed commit leaves transaction:", db.in_transaction)
db.execute("INSERT INTO parent VALUES(10)")
db.execute("COMMIT")
assert not db.in_transaction
assert db.execute("PRAGMA foreign_key_check").fetchall() == []
print("repaired and committed:", not db.in_transaction)
try:
db.execute("INSERT INTO child VALUES(2, 20)")
except sqlite3.IntegrityError:
assert not db.in_transaction
print("without BEGIN: rejected at statement end")
else:
raise AssertionError("orphan accepted outside transaction")
db.execute("BEGIN")
db.execute("INSERT INTO child VALUES(3, 30)")
rejected_commit()
db.execute("ROLLBACK")
rows = db.execute("SELECT id FROM child ORDER BY id").fetchall()
assert rows == [(1,)] and not db.in_transaction
print("after rollback:", rows, db.in_transaction)
finally:
if db.in_transaction:
db.execute("ROLLBACK")
db.close()第一行确认 failed commit leaves transaction 为 True，子记录一仍能在当前连接里查到。随后补入示例约定的父记录，重新 COMMIT 成功，事务结束，foreign_key_check 没有返回违规项。这说明第一次失败挡住的是提交，没有自动撤回全部事务内容。
在修复与撤销之间作明确选择
如果缺失父记录只是导入顺序问题，且它本来就在这批合法数据里，可以继续完成同一事务再提交。如果引用本身错误、输入不完整或业务要求整批失败，应主动 ROLLBACK。最后一段验证了这条分支：子记录三被撤销，先前已提交的一仍保留。
示例补入编号十，是因为教学数据明确规定这条父记录应当存在。真实业务不能为了让约束通过而临时捏造父对象；应该根据已确认的输入修正引用、补齐真实数据，或者放弃本次事务。数据库约束不会替应用判断哪种修复符合业务。
提交失败后继续执行普通语句，它们可能仍属于这个未结束事务。若把连接直接归还连接池，后一个任务就可能接到未完成的状态。因此错误处理要同时决定数据去向和连接状态，记录当前事务是否仍开着，并在交还前完成收尾。
延迟需要实际存在的事务范围
中间的反例没有执行 BEGIN。单条插入结束时，其隐式事务就要提交，缺失父记录因而立即导致该调用失败。声明延迟约束并不意味着可以分散到任意多个自动提交语句里补关系；要跨语句协调，就必须有共同的显式事务。
延迟也不等于把所有检查都推后。这里讨论的是指定外键的检查时机，主键冲突、非空要求等其他约束不能据此忽略。外键动作中的 RESTRICT 还有立即阻止父键变更的规则，即使关联的外键本身声明为延迟，也要单独核对。
这段恢复逻辑只针对已经确认的延迟外键失败。磁盘错误、连接故障和其他事务错误有各自的状态边界，不能一律照搬“继续补数据再提交”。保留具体异常和状态检查，才能决定下一步是否仍可在同一事务中完成。
验收应同时覆盖插入后、失败提交后、修复提交后和主动回滚后。只断言 INSERT 没报错，会漏掉最终约束；只断言 COMMIT 抛错，又会漏掉残留事务。把完成信号放在成功提交之后，调用方才不会提前宣布整批导入已保存。
参考资料
资料核对日期：2026年10月2日（北京时间）。示例在 Linux、Python 3.12.14、SQLite 3.53.1 中独立运行。
