默认情况下，SQLite 真的不保留外键约束的数据完整性吗? [英] Does SQLite really not preserve data integrity of foreign key constraints by default?

问题描述

较新版本的 SQLite 支持外键约束.可以定义

Newer versions of SQLite support foreign key constraints. It is possible to define

CREATE TABLE MASTER (_ID INTEGER PRIMARY KEY, ...);
CREATE TABLE SERVANT (_ID INTEGER PRIMARY KEY, MASTERID INTEGER, 
  FOREIGN KEY(MASTERID) REFERENCES MASTER(_ID);

根据文档，默认情况下NO ACTION"用于 ON DELETE 和 ON UPDATE.但与其他 DBS 相反，NO ACTION"似乎并不意味着不执行删除或更新.这似乎意味着没有采取任何措施来保持完整性，至少根据我的测试(*)并且如果我理解 文档 正确:

According to the documentation by default "NO ACTION" is used for ON DELETE and ON UPDATE. But contrary to other DBS "NO ACTION" seems not to mean, that delete or update is not performed. It seems to mean that nothing is done to preserve integrity, at least according to my tests(*) and if I understand the documentation right:

配置NO ACTION"意味着:当父键被修改时或从数据库中删除，不采取特殊操作.

Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.

因此

INSERT INTO MASTER (_ID) VALUES (1);
INSERT INTO SERVANT (_ID, MASTERID) VALUES (1,1);
DELETE FROM MASTER;

给我一​​个空的 MASTER 表和一个外键指向无处的 SERVANT 表.

gives me an empty MASTER table and a SERVANT table with a foreign key pointing into nowhere.

任何人都可以确认这种行为并解释为什么以这种方式实施吗?还是我必须配置一些东西才能使外键支持工作?我是 SQLite 开发的新手，所以请原谅我，如果这是一个愚蠢的问题.

Can anyone confirm this behaviour and maybe explain why it is implemented that way? Or do I have to configure something to make foreign key support work? I am new to SQLite development, so please foregive me, if this is a stupid question.

(*) 我的测试有缺陷，请参阅 我在下面的回答.

(*) my tests were flawed, see my answer below.

推荐答案

我会尝试自己给出答案:

I will try to give an answer myself:

不，如果配置正确，SQLite 在这种情况下会保持数据完整性.默认情况下使用NO ACTION"，如果引用表中仍然存在引用键(用 3.7.x 测试)，这将禁止删除或更新主键.我的错是我不知道 PRAGMA foreign_keys = ON; 必须为 每个 到数据库的新连接配置.

No, if configured right, SQLite preserves data integrity in this situation. "NO ACTION" is used by default and this prohibits deletion or update of a master key if there is still a refering key from an referencing table (tested with 3.7.x). My fault was that I was not aware that PRAGMA foreign_keys = ON; must be configured for every new connection to the database.

我认为 SQLite 文档在此处误导.

I think the SQLite documentation is misleading here.

这篇关于默认情况下，SQLite 真的不保留外键约束的数据完整性吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！