SQLite 明明声明了外键，为什么孤儿行还能写入

2026-11-13 #sqlite#integrity#连接池

迁移 SQL 已声明外键，某个导入工具却写出了不存在的父记录。先在出问题的连接上查询 PRAGMA foreign_keys。启用外键约束是每个连接自己的设置，已有数据还要单独核验。

1. 两条连接写同一张表

保存为 foreign_key_lab.py，运行 python foreign_key_lab.py。

from contextlib import closing
from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory

with TemporaryDirectory() as tmp:
    path = Path(tmp) / "lab.db"
    with closing(sqlite3.connect(path)) as importer, closing(sqlite3.connect(path)) as app:
        importer.execute("PRAGMA foreign_keys=OFF")
        importer.executescript("""
            CREATE TABLE orders(id INTEGER PRIMARY KEY);
            CREATE TABLE items(id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL
                               REFERENCES orders(id));
        """)
        importer.execute("BEGIN")
        importer.execute("PRAGMA foreign_keys=ON")
        assert importer.execute("PRAGMA foreign_keys").fetchone() == (0,)
        print("enable inside transaction: still off")
        importer.execute("INSERT INTO items VALUES (1, 99)")
        importer.commit()

        app.execute("PRAGMA foreign_keys=ON")
        assert app.execute("PRAGMA foreign_keys").fetchone() == (1,)
        try:
            with app:
                app.execute("INSERT INTO items VALUES (2, 99)")
        except sqlite3.IntegrityError as error:
            assert error.sqlite_errorname == "SQLITE_CONSTRAINT_FOREIGNKEY"
            print(error.sqlite_errorname)
        else:
            raise AssertionError("expected foreign-key rejection")

        violations = app.execute("PRAGMA foreign_key_check").fetchall()
        assert violations == [("items", 1, "orders", 0)]
        print("existing violations:", violations)
        with app:
            app.execute("INSERT INTO orders VALUES (99)")
        assert app.execute("PRAGMA foreign_key_check").fetchall() == []
        print("after restoring parent: no violations")

本机实测输出如下。

enable inside transaction: still off
SQLITE_CONSTRAINT_FOREIGNKEY
existing violations: [('items', 1, 'orders', 0)]
after restoring parent: no violations

2. 把开关放进连接初始化

池中新建物理连接时先执行启用命令，再读回 1，然后才允许框架开始事务。批处理、迁移程序和后台 worker 同样要走初始化路径。应在有多个实际物理连接的环境里测试，避免单连接测试掩盖漏配。

foreign_key_check 每行依次给出子表、行标识、父表和外键编号。先查编号对应哪个约束，再按业务决定补父记录、修正关联或移除孤儿行。本例知道父记录属于测试数据，因此用补建父记录演示核验闭环。

问题	检查
某一条新连接仍能写孤儿行	在该连接读回 PRAGMA foreign_keys
打开后仍存在坏数据	用 PRAGMA foreign_key_check 扫描历史记录
删除父记录很慢	检查子表外键列是否具备适用索引

小结

把外键开启纳入每条物理连接的初始化，把历史数据审计纳入迁移验收。

数据来源与检索时间

检索时间 2026-11-13。

来源	URL	对应内容
SQLite Foreign Key Support	https://sqlite.org/foreignkeys.html#fk_enable	连接级外键开关、事务内设置及索引
SQLite PRAGMA	https://www.sqlite.org/pragma.html#pragma_foreign_key_check	历史违反记录与返回列

核验说明

Python 3.11.9 / SQLite 3.45.1 用两个文件数据库连接验证。

#sqlite #integrity #连接池