SQLite 外键已经声明，为什么还能插入无效数据？

在 SQLite 中，表里写了 REFERENCES，不代表每个连接都会执行外键检查。连接上的设置不同，同一张表就可能同时接受和拒绝无效引用。还有一个容易忽略的地方：事务中执行 PRAGMA foreign_keys = ON 不会报错，但也不会改变设置。如果 SQLite 编译时没有包含外键支持，或者读回的设置不是 1，则不能依赖外键约束。

下面用两个连接演示这个区别。实测环境为 Windows 11、Python 3.13.17 和 SQLite 3.50.4；程序只用 Python 标准库和内存数据库，不联网，也不创建数据库文件。代码使用的 sqlite_errorcode 和 sqlite_errorname 属性需要 Python 3.11 或更高版本；下列结果仅在上述环境实测。

把代码保存为 sqlite_foreign_keys_demo_20261103.py，用已经配置好的 Python 运行：

python -B sqlite_foreign_keys_demo_20261103.py

import sqlite3

DB = "file:foreign_key_demo?mode=memory&cache=shared"
a = sqlite3.connect(DB, uri=True, isolation_level=None)
b = sqlite3.connect(DB, uri=True, isolation_level=None)


def foreign_keys(conn):
    return conn.execute("PRAGMA foreign_keys").fetchone()[0]


def rejected(conn, child_id):
    try:
        conn.execute("INSERT INTO child VALUES (?, ?)", (child_id, 999))
    except sqlite3.IntegrityError as exc:
        if exc.sqlite_errorcode != sqlite3.SQLITE_CONSTRAINT_FOREIGNKEY:
            raise
        return exc.sqlite_errorname
    raise AssertionError("invalid reference was accepted")


try:
    # 显式设置，避免依赖 SQLite 编译时的默认值。
    a.execute("PRAGMA foreign_keys = OFF")
    b.execute("PRAGMA foreign_keys = ON")
    a.execute("CREATE TABLE parent (id INTEGER PRIMARY KEY)")
    a.execute("CREATE TABLE child (id INTEGER PRIMARY KEY, "
              "parent_id INTEGER NOT NULL REFERENCES parent(id))")
    print("A/B foreign_keys:", foreign_keys(a), foreign_keys(b))
    assert foreign_keys(a) == 0 and foreign_keys(b) == 1

    a.execute("INSERT INTO child VALUES (1, 999)")
    print("B sees:", b.execute("SELECT * FROM child").fetchall())
    print("B rejects invalid row:", rejected(b, 2))

    a.execute("BEGIN")
    a.execute("PRAGMA foreign_keys = ON")  # 事务中设置不生效
    print("A inside transaction:", foreign_keys(a))
    assert foreign_keys(a) == 0
    a.execute("ROLLBACK")
    a.execute("PRAGMA foreign_keys = ON")
    print("A after transaction:", foreign_keys(a))
    assert foreign_keys(a) == 1
    print("A rejects invalid row:", rejected(a, 2))

    a.execute("INSERT INTO parent VALUES (1)")
    a.execute("INSERT INTO child VALUES (3, 1)")
    print("child rows:", a.execute("SELECT * FROM child ORDER BY id").fetchall())
    print("integrity_check:", a.execute("PRAGMA integrity_check").fetchone()[0])
    print("foreign_key_check:", a.execute("PRAGMA foreign_key_check").fetchall())
finally:
    b.close()
    a.close()

本机输出：

A/B foreign_keys: 0 1
B sees: [(1, 999)]
B rejects invalid row: SQLITE_CONSTRAINT_FOREIGNKEY
A inside transaction: 0
A after transaction: 1
A rejects invalid row: SQLITE_CONSTRAINT_FOREIGNKEY
child rows: [(1, 999), (3, 1)]
integrity_check: ok
foreign_key_check: [('child', 1, 'parent', 0)]

两个连接通过同一个命名内存库共享数据。示例用 isolation_level=None 关闭 Python 的隐式事务，并显式开启一个事务。A 关闭外键检查后写入 (1, 999)；B 能看到这条记录，却拒绝了自己尝试写入的无效引用。事务中的 A 无法开启检查；回滚事务后再开启，才会拒绝相同的无效写入。(3, 1) 引用已存在的父记录，所以可以写入。

开启检查不会自动修复历史数据。输出末尾的 foreign_key_check 表示 child 表的 rowid 为 1 的记录引用了 parent，违反外键编号 0。此例中 id 是 INTEGER PRIMARY KEY，因此也是该行的 rowid。虽然 integrity_check 返回 ok，它不检查外键；排查旧数据时要单独使用 foreign_key_check。检查结果应结合业务规则修复，不应直接批量删除。

用于实际项目时，在每个新连接建立后、开启事务前设置并读取 PRAGMA foreign_keys，确认值为 1；连接池也要在每个物理连接初始化时处理。不要依赖默认值，SQLite 的编译配置可能影响它。对于必须有父记录的字段，还要声明 NOT NULL：外键列允许 NULL 时，空值不会要求匹配父记录。

参考资料：SQLite 官方文档《Foreign Key Support》《PRAGMA foreign_key_check》；Python 官方文档《sqlite3 — DB-API 2.0 interface for SQLite databases》。

本文借助 AI 辅助整理，示例经运行核验。