SQLite3违反外键约束未抛出错误的问题咨询

问题复现操作 #

你执行的操作如下：

$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open dumb.db
sqlite> create table a (aid integer primary key, name text);
sqlite> create table b (bid integer, aid integer, name text, foreign key(aid) references a(aid));
sqlite> insert into b values (1,1,'wtf');
sqlite> select * from b;
1|1|wtf
sqlite> select * from a;
sqlite>

根因说明 #

你对标准关系型数据库外键约束的认知没有错误，出现不符合预期的执行结果是SQLite 3的默认配置特性导致的：

• SQLite 3默认关闭外键约束检查功能，该设计是为了向后兼容旧版本SQLite的业务代码，避免新增外键语法后旧程序运行报错。
• 你可以在当前sqlite会话中执行如下命令确认外键检查状态，默认返回值为0（即关闭状态）：

  PRAGMA foreign_keys;
  

• 开启当前会话的外键约束检查，执行如下命令即可，该配置仅对当前连接生效，断开数据库重连后会恢复默认关闭状态：

  PRAGMA foreign_keys = ON;
  

• 开启外键检查后再执行插入语句insert into b values (1,1,'wtf');，就会触发FOREIGN KEY constraint failed的报错，完全符合标准外键约束的预期行为。

内容的提问来源于stack exchange，提问作者Josh Vander Hook