SQLite3中外键约束失效问题求助

环境信息 #

• Python版本：3.8.10
• sqlite3.version：2.6.0
• sqlite3.sqlite_version：3.31.1

问题场景 #

我创建了两张表：blocklist和blocklist_reasons，结构如下：

blocklist_reasons
id | reason
--------------------------
1  | 不支持该来源

blocklist
id | link            | reason
-----------------------------
1  | https://mega.nz | 1

blocklist.reason字段引用blocklist_reasons.id，已设置外键约束。但奇怪的是，我可以插入任意数值到blocklist.reason中，数据库都能接受：

sqlite> INSERT INTO blocklist(link, reason) VALUES ('https://example.com', 2);
sqlite> -- 即使blocklist_reasons中不存在ID为2的行也没有报错？？？
sqlite> SELECT * FROM blocklist;
1|https://mega.nz|1
2|https://example.com|2

我通过以下代码复现了该问题：

-- 创建原因表并添加ID为1的原因
CREATE TABLE blocklist_reasons(
    id INTEGER PRIMARY KEY,
    reason TEXT NOT NULL UNIQUE
);
INSERT INTO blocklist_reasons(reason) VALUES ('不支持该来源');
SELECT * FROM blocklist_reasons;
1|不支持该来源

-- 创建blocklist表并插入reason为2的行，无报错
CREATE TABLE blocklist(
    id INTEGER PRIMARY KEY,
    link TEXT NOT NULL,
    reason INTEGER NOT NULL,
    FOREIGN KEY (reason) REFERENCES blocklist_reasons(id)
);
INSERT INTO blocklist(link, reason) VALUES ('https://mega.nz', 2);
SELECT * FROM blocklist;
1|https://mega.nz|2

可以看到，我插入了reason为2的行，即使blocklist_reasons中没有ID为2的行也能成功，为何外键约束没有触发错误？

解答 #

SQLite默认禁用外键约束，即便创建表时声明了外键，也必须手动开启外键检查才能让约束生效。

你可以通过两种方式解决：

1. Python连接时开启约束 #

使用sqlite3库建立连接后，执行开启命令：

import sqlite3
conn = sqlite3.connect('你的数据库文件.db')
conn.execute("PRAGMA foreign_keys = ON;")

2. SQL会话中临时开启 #

每次连接数据库后，先执行这条SQL命令：

PRAGMA foreign_keys = ON;

开启约束后，再插入不存在的reason值就会触发错误：

sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO blocklist(link, reason) VALUES ('https://example.com', 2);
Error: FOREIGN KEY constraint failed

注意：PRAGMA foreign_keys是会话级设置，每次重新连接数据库都需要重新开启，除非在连接配置中默认启用。

内容的提问来源于stack exchange，提问作者Cas