JavaLYG

SQLite 声明了外键却写入孤儿行:用连接初始化与事务外读回定位

SQLite 表明明写了 REFERENCES,Python 导入脚本却把找不到父记录的数据写进去了。先别重建表:在实际执行写入的那条连接上读 PRAGMA foreign_keys。如果返回 0,检查初始化是否跑在事务开始之后。下面用两个连接打开同一份临时库,复现为什么一条连接允许写入,另一条却能挡住;最后用 foreign_key_check 找出已经留下的孤儿行。

一、外键定义在表里,外键检查开在连接上

假设 child.parent_id 引用 parent.id。子表里有一行指向编号 999,父表里却没有 999,这就是本文说的孤儿行。排查时先把两个问题分开:PRAGMA foreign_key_list(child) 能看到表里声明了哪些引用;PRAGMA foreign_keys 才能告诉你当前连接有没有启用检查。前一个查得出内容,不代表后一个返回 1。[1][2]

这个开关不是写进库文件的全局设置。迁移脚本开过一次,稍后启动的导入脚本不会因此自动继承。应用有连接池也一样:盯住新建的物理连接,而不是只在进程启动时随手取一条连接设置。排障日志最好同时报运行时 SQLite 版本、连接来自哪个入口,以及这一条连接读回的开关值;不必把业务记录或数据库绝对路径写进去。[1][2]

二、执行没报错,可能是整个设置被忽略了

BEGIN 或 SAVEPOINT 仍在生效时,SQLite 不会改变 foreign_keys,也不会因为这个原因给你报错。开着时试图关掉同样没用。不要拿执行返回的游标当配置回执:执行完后要在同一条连接上查一次 PRAGMA foreign_keys,并检查返回的是不是单行的 1。如果连 0 或 1 都没有,按不可用处理;官方文档指出,缺少所需支持的 SQLite 构建可能没有这行结果。[1][2]

Python 3.12 新增了 sqlite3.connect(..., autocommit=...) 参数。若显式指定 autocommit=False,驱动在连接建立时就打开事务,commit() 和 rollback() 结束当前事务后也会立即开启新的事务。因此,“连上就设置”仍可能太迟,“先提交再设置”也未必能腾出空档。看底层事务状态用 in_transaction,别把 Python 的 autocommit 选项直接当成底层此刻的事务状态。[3]

SQLite 外键开关:事务开始前执行并读回,事务进行中修改无效,每个连接单独配置
对照的是同一条连接的两个设置时机。事务外设置 ON 后读取到 1,才算确认启用。

三、把开关放进新建物理连接的函数

下面的工厂先用 autocommit=True 建立一条新连接,不先执行写入或手工开启事务;它开启外键、读回 1,再切换到业务要求的 autocommit=False。任何初始化失败都关闭这条连接,避免把没有完成初始化的对象交给调用方。这个顺序只用于尚未承载业务的连接,不要照搬到已有未提交工作的连接上:切到 autocommit=True 会提交待处理事务。[2][3]

如果项目仍用旧式事务控制,就按那个模式安排初始化:在新连接刚创建、还没有隐式写事务或显式事务时设置并读回,而不是把 autocommit=True 参数抄到 Python 3.11。若用的是 ORM,先确认它何时真正创建底层连接、何时开启事务。把读回失败当作连接初始化失败,而不是等第一笔坏数据落库后再猜连接从哪里来。[2][3]

四、用两个连接复现,不碰业务库

把下面代码保存成 foreign_keys_demo.py,用 Python 3.12 或更新版本运行 python3 foreign_keys_demo.py。实验用标准库新建临时文件,结束后自动清理。为了不依赖 SQLite 构建的默认值,连接 A 在事务外明确关闭检查,然后写入一个孤儿。连接 B 对同一文件开启检查:新孤儿被拒绝、合法行能提交,而 A 的开关仍为 0。最后重新连接,检查显式选择 False 时为何仅提交不够。

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


def read_fk(con):
    row = con.execute('PRAGMA foreign_keys').fetchone()
    if row not in ((0,), (1,)):
        raise RuntimeError('SQLite 未返回可用的外键开关')
    return row[0]


def open_checked(db):
    # Python 3.12+:先处于 SQLite 自动提交状态,再切换事务模式。
    con = sqlite3.connect(db, autocommit=True)
    try:
        con.execute('PRAGMA foreign_keys = ON')
        if read_fk(con) != 1:
            raise RuntimeError('外键检查未开启,拒绝使用该连接')
        con.autocommit = False
        return con
    except BaseException:
        con.close()
        raise


def check(label, actual, expected):
    if actual != expected:
        raise AssertionError(f'{label}: {actual!r} != {expected!r}')
    print(f'通过:{label}')


schema = '''
CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(id INTEGER PRIMARY KEY,
                   parent_id INTEGER NOT NULL REFERENCES parent(id));
INSERT INTO parent VALUES (1);
'''
insert = 'INSERT INTO child VALUES (?, ?)'

with TemporaryDirectory() as directory:
    db = Path(directory) / 'demo.sqlite'
    with closing(sqlite3.connect(db, autocommit=True)) as a:
        # 反例显式关闭,不依赖 SQLite 构建的默认值。
        a.execute('PRAGMA foreign_keys = OFF')
        a.executescript(schema)
        check('表里有外键定义', bool(a.execute(
            'PRAGMA foreign_key_list(child)').fetchall()), True)
        check('事务外明确关闭', read_fk(a), 0)
        a.execute('BEGIN')
        a.execute('PRAGMA foreign_keys = ON')
        check('BEGIN 内开启无效', read_fk(a), 0)
        a.execute('ROLLBACK')  # 显式 SQL 事务用显式 SQL 结束。
        a.execute('SAVEPOINT sample')
        a.execute('PRAGMA foreign_keys = ON')
        check('SAVEPOINT 内开启无效', read_fk(a), 0)
        a.execute('RELEASE sample')
        a.execute(insert, (10, 999))  # 反例:提交一个孤儿行。
        check('关闭时真的写入孤儿', a.execute(
            'SELECT id FROM child').fetchall(), [(10,)])

        with closing(open_checked(db)) as b:
            check('新连接启用后切到事务模式',
                  (read_fk(b), b.in_transaction), (1, True))
            check('同一文件的旧连接仍为关闭', read_fk(a), 0)
            b.execute('PRAGMA foreign_keys = OFF')
            check('事务内关闭也无效', read_fk(b), 1)
            try:
                b.execute(insert, (11, 999))
            except sqlite3.IntegrityError as exc:
                check('新孤儿被外键约束拒绝',
                      exc.sqlite_errorname, 'SQLITE_CONSTRAINT_FOREIGNKEY')
            else:
                raise AssertionError('预期外键约束拒绝插入')
            b.rollback()
            check('rollback 后仍在新事务中', b.in_transaction, True)
            b.execute(insert, (12, 1))
            b.commit()
            check('有效写入提交后由另一连接看到', a.execute(
                'SELECT id FROM child ORDER BY id').fetchall(), [(10,), (12,)])
            check('开启后旧违规行仍可定位', b.execute(
                'PRAGMA main.foreign_key_check').fetchall(),
                [('child', 10, 'parent', 0)])
            check('常规完整性检查不报告该外键违规', b.execute(
                'PRAGMA main.integrity_check').fetchall(), [('ok',)])

    with closing(sqlite3.connect(db, autocommit=False)) as c:
        check('直接用 False 连接时事务已打开', c.in_transaction, True)
        before = read_fk(c)
        c.execute(f'PRAGMA foreign_keys = {1 - before}')
        check('False 模式连接后改开关无效', read_fk(c), before)
        c.commit()
        c.execute(f'PRAGMA foreign_keys = {1 - before}')
        check('仅 commit 再设置仍无效',
              (read_fk(c), c.in_transaction), (before, True))

本次在 Python 3.14.3、SQLite 3.26.0 的临时库跑过这段代码。实验里的外键是普通即时约束,错误发生在插入时;如果业务表把约束设为延迟检查,错误可能到提交时才出现,验收别只执行第一条写入。这里也用 closing() 管理连接寿命,和事务是否提交是两件事。[1][3]

五、旧孤儿不会因为打开开关而消失

启用检查解决的是后续约束执行,不会替你补出过去缺失的父记录,也不会自动删掉历史子记录。实验里的连接 B 已经拒绝新的坏数据,仍能用 PRAGMA main.foreign_key_check 找到旧的第 10 行。这个查询每条违规返回一行,依次是子表名、子行的 rowid(无 rowid 的表返回 NULL)、被引用的父表名和外键序号;最后一列可与子表的 foreign_key_list 对照。[2]

普通的 PRAGMA integrity_check 并不报告外键错误,所以看到 ok 也不能省掉外键检查。清历史数据前,应在副本上先判断是子行写错、父行导漏,还是迁移映射有偏差。尤其不要拿输出的 rowid 直接批量删生产数据,它未必就是业务编号。若挂载了其他数据库,明确要查哪个 schema;示例限定 main。处理后在目标库副本重新扫描,并用业务对账检查修复结果。[2]

排查一份刚接手的库时,可以先记录表、行号和外键序号,再按引用关系查对应值。一个子表有多条外键时,同一行可能在结果里出现不止一次,按表名和行号合并前,要保留每条约束的编号。只想检查某个子表时,可以给 foreign_key_check 指定表名;这不能当作全库扫描的结果。[2]

六、按读回结果分流

观察到的结果先查什么下一步
声明存在,开关查回 0实际执行业务写入的连接有没有初始化在每条新物理连接的事务外开启并确认
刚执行 ON,查回仍是 0是否存在 BEGIN、SAVEPOINT 或驱动已打开事务把设置前移到新连接初始化;不要提交未知业务事务
读不到开关结果实际链接的 SQLite 版本和构建阻止启用该连接,核对部署制品
开关为 1,历史扫描仍有结果违规来自旧导入还是错误迁移核对父子记录,按业务规则修复并复扫
开关为 1,预期失败的插入却接受了写入和探针是否在同一连接、测试值是否真缺父行、是否延迟约束在副本测到预期失败点,包括必要的提交

七、上线前做一次正反验证

不要只从调试终端新开一条连接检查。在 Web 进程的连接池、批处理脚本和迁移程序里,分别沿着实际写入路径确认每次新建物理连接都完成初始化,尤其是断线重建之后。让回归包含两条互不干扰的写入:缺父记录的一条必须在正确的时机失败,存在父记录的一条必须提交后能从另一条连接读到。这样既能发现规则没开,也能发现初始化直接把正常写入挡住了。

排查时还有一种很像的现象:测试用了父表里已经存在的编号,因此无论开关是零还是一,写入都成功。这样的正例只能证明正常路径没被挡住,不能证明检查已打开。反例要选一个明确缺失的编号,并让子表本身的其他字段满足约束,否则捕获到的也许是主键冲突或非空错误。测试代码应区分具体错误,不用一个宽泛的异常捕获替代外键门禁。跑这类反例时,使用临时库或专门的测试副本;在检查关闭的连接上,坏行可能真的被提交。

若同一批导入有的任务报错、有的照常结束,先按写入入口拆开核对。比如接口线程与命令行工具虽然打开同一文件,连接创建逻辑却往往不同。把连接初始化收进共享工厂后,再给绕过工厂的运维脚本补检查;只修 Web 那一处,并不能说明离线任务也受保护。

本地回归只验证了临时文件中普通外键和两种事务启动方式,不代表连接池、并发导入或生产迁移已验收。业务历史数据还要另跑完整扫描,记录发现多少违规和如何处置。如果发现某条写入路径未初始化,优先暂停那条导入路径,不要为了让批次看起来顺利就把约束关回去。

八、官方参考

🔕 评论已关闭