SQLite中PRAGMA foreign_keys=ON关联外键表机制及重命名表异常

我发现SQLite处理外键关系的方式和预期不符：调整数据模型时，我把表A拆分成表A和A1，步骤是先将原表A重命名为A_tmp，创建新表A和A1并从A_tmp导入数据，验证完成后删除A_tmp。但开启PRAGMA foreign_keys=ON时，其他引用A表的表（比如定义了REFERENCES A(ID) ON DELETE CASCADE的表B）里的数据被删除了，尽管新表A包含所有需要的ID。想请教：SQLite的PRAGMA foreign_keys=ON是怎么关联外键表的？是不是通过表名关联？这似乎是导致关联数据被删的唯一原因。

演示会话 #

以下是问题的演示过程（省略部分非必要输出）：

SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys=ON;
sqlite> CREATE TABLE A (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, A_1 VARCHAR(32) NOT NULL);
sqlite> CREATE TABLE B (ID INTEGER PRIMARY KEY NOT NULL REFERENCES A(ID) ON DELETE CASCADE, B_1 VARCHAR(32) NOT NULL);
sqlite> INSERT INTO A (ID, A_1) VALUES (1, 'foo-bar');
sqlite> INSERT INTO B (ID, B_1) VALUES (1, 'anything');
sqlite> .dump
#...
INSERT INTO A VALUES(1,'foo-bar');
INSERT INTO B VALUES(1,'anything');
#...
sqlite> ALTER TABLE A RENAME TO A_tmp;
sqlite> CREATE TABLE A1 (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, A1_1 VARCHAR(32) NOT NULL);
sqlite> CREATE TABLE A (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, A_1 INTEGER NOT NULL REFERENCES A1(ID) ON DELETE RESTRICT, A_2 VARCHAR(16) NOT NULL);
sqlite> INSERT INTO A1 (ID, A1_1) VALUES (1, 'foo');
sqlite> INSERT INTO A (ID, A_1, A_2) VALUES (1, 1, 'bar');
sqlite> .dump
INSERT INTO A_tmp VALUES(1,'foo-bar');
INSERT INTO B VALUES(1,'anything');
INSERT INTO A1 VALUES(1,'foo');
INSERT INTO A VALUES(1,1,'bar');
# 数据都在，删除旧表A_tmp
sqlite> DROP TABLE A_tmp;
sqlite> .dump
INSERT INTO A1 VALUES(1,'foo');
INSERT INTO A VALUES(1,1,'bar');
# 明明新表A的ID还存在，表B里的行却消失了！

问题原因 #

SQLite的外键约束不是通过表名关联，而是绑定到创建外键时目标表的内部对象标识符（OID）。

执行ALTER TABLE A RENAME TO A_tmp时，原表A仅修改了名称，内部OID保持不变，表B的外键依然指向这个改名后的A_tmp表。后续创建的新表A是全新的数据库对象，拥有独立的OID，表B的外键不会自动切换到新表A上。

因此删除A_tmp时，由于表B的外键关联的是A_tmp，且设置了ON DELETE CASCADE，SQLite会判定A_tmp中的主键被删除，进而级联删除表B中对应的行——哪怕新表A存在相同ID值，两者在SQLite中是完全无关的表。

解决方法 #

方法一：临时关闭外键约束

1. 执行PRAGMA foreign_keys=OFF;关闭外键检查
2. 完成表重命名、拆分、数据导入操作
3. 重建表B到新表A的外键约束（可先删除旧外键再添加新约束，或重建表B并导入数据）
4. 执行PRAGMA foreign_keys=ON;重新开启外键约束

方法二：重建关联表

• 创建新表A1和临时表A_new并导入数据
• 删除原表A和表B
• 将A_new重命名为A，重建表B并导入数据，同时设置正确的外键约束

两种方法都能确保外键绑定到正确的目标表，避免意外的级联删除。

内容的提问来源于stack exchange，提问作者U. Windl