DELETE级联上的INSERT或REPLACE+外键工作得太好

我目前正在尝试创建一个sqlite数据库，在那里我可以从另一个sqlte数据库导入一个表（无法附加），并向每列添加一些额外的数据。

由于没有INSERT OR UPDATE，我想出了这个：
我想把数据分成两个表，然后连接它们，这样我就可以把整个导入转储到一个表中，替换所有更改的数据，并单独管理额外的数据，因为导入时不会更改。

第一个表（我们称之为base_data）看起来像

local_id | remote_id | base_data1 | base_data2 | ...
---------+-----------+------------+------------+----

除了local_id之外，所有东西都只是远程数据库的镜像（我可能会添加一个同步时间戳，但现在这无关紧要）。

第二个表看起来类似，但将remote_id设置为外键

remote_id | extra_data1 | extra_data2 | ...
----------+-------------+-------------+----
   CREATE TABLE extra_data (
       remote_id INTEGER 
           REFERENCES base_data(remote_id)
           ON DELETE CASCADE ON UPDATE CASCADE
           DEFERRABLE INITIALLY DEFERRED,
       extra_data1 TEXT,
       extra_data2 TEXT,
       /* etc */
   )

现在，我的想法是简单地使用INSERT OR REPLACE INTO base_data ...值，因为我从中导入的数据库没有同步时间戳或任何东西，我必须比较所有内容，以找出我有什么行要UPDATE/什么要INSERT。

但问题在于：INSERT OR REPLACE实际上是DELETE，后面跟着INSERT，删除部分触发外键ON DELETE，我认为我可以通过设置约束DEFERRED来阻止它。如果我在事务中封装INSERT OR REPLACE，它也不起作用。它总是删除我的额外数据，尽管语句后面存在相同的外键。

是否可以停止ON DELETE触发，直到INSERT OR REPLACE结束？也许是一些特殊的事务模式/pragma？

如果我用类似的触发器替换ON DELETE CASCADE部分，似乎可以工作

CREATE TRIGGER on_delete_trigger
   AFTER DELETE ON base_data
   BEGIN
       DELETE FROM extra_data WHERE extra_data.remote_id=OLD.remote_id;
   END;

该触发器仅由DELETE语句触发，到目前为止应该可以解决我的问题。

^{（OP在问题中提供的答案）}

jmathew引用文件提供的附加信息：

当REPLACE冲突解决策略删除行以满足约束时，当且仅当启用递归触发器时，才会触发删除触发器。

假设您在引用表中的主键上只有一个外键关系（就像您在示例中所做的那样），这对我来说是一个相当无痛的解决方案

只需禁用外键检查，运行替换查询，然后再次启用外键。

如果replace查询是唯一一个在外键被禁用时运行的查询，那么可以确保没有外键被破坏。如果插入新行，则没有任何内容可以链接到该行；如果替换行，则查询不会删除现有行或更改其主键，因此一旦重新启用外键，约束仍将保持不变。

SQLlite代码看起来像这样：

PRAGMA foreign_keys=OFF;
INSERT OR REPLACE ...;
PRAGMA foreign_keys=ON;

关于这种行为的原因，PostgreSQL团队有一个解释：

是的，据我们所知，这符合SQL规范。约束检查可以延期至交易结束，但"；参考动作"；不是可延期。它们总是发生在触发语句期间。对于实例SQL99将级联删除的结果描述为引用行是"0"；标记为删除"；立即，然后

所有标记为删除的行都将被有效删除在SQL语句的末尾，在检查任何完整性约束

相关内容

最新更新

热门标签：