---
title: "SQLite 明明声明了外键，为什么孤儿行还能写入"
date: 2026-11-13
slug: sqlite-foreign-key-connection-setup
canonical: https://kernelranch.com/blog/2026/11/sqlite-foreign-key-connection-setup
markdown_url: https://kernelranch.com/blog/2026/11/sqlite-foreign-key-connection-setup.md
tags: ["sqlite", "integrity", "连接池"]
---迁移 SQL 已声明外键，某个导入工具却写出了不存在的父记录。先在出问题的连接上查询 PRAGMA foreign_keys。启用外键约束是每个连接自己的设置，已有数据还要单独核验。

## 1. 两条连接写同一张表

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

```python
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")
```

本机实测输出如下。

```text
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 用两个文件数据库连接验证。