INSERT OR REPLACE +外键ON DELETE CASCADE工作太好了 [英] INSERT OR REPLACE + foreign key ON DELETE CASCADE working too good

问题描述

我目前正在尝试创建一个 sqlite 数据库，我可以在其中从另一个 sqlite 数据库(无法附加)导入一个表，并向每列添加一些额外的数据.

I am currently trying to create an sqlite database where I can import a table from another sqlite database (can't attach) and add some extra data to each column.

因为没有 INSERT OR UPDATE 我想出了这个:
我正在考虑将数据拆分为两个表，然后再将它们连接起来，这样我就可以将整个导入转储到一个表中，替换更改的所有内容并单独管理额外的数据，因为导入时不会更改.

Since there is no INSERT OR UPDATE I came up with this:
I was thinking about splitting the data into two tables and join them afterwards so I can just dump the whole import into one table replacing everything that changed and manage the extra data separately since that does not change on import.

第一个表(我们称之为base_data)看起来像

The first table (let's call it base_data) would look like

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

除了 local_id 之外，一切都只是远程数据库的镜像(我可能会添加一个同步时间戳，但现在无关紧要).

besides the local_id everything would just be a mirror of the remote database (I'll probably add a sync timestamp but that does not matter now).

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

The second table would look similar but has remote_id set as foreign key

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 什么.

Now my idea was to simply INSERT OR REPLACE INTO base_data ... values because the database I import from has no sync timestamp or whatsoever and I would have to compare everything to find out what row I have to UPDATE / what to INSERT.

但问题就在这里:INSERT OR REPLACE 实际上是一个 DELETE 后跟一个 INSERT 并且删除部分触发了外键 ON DELETE 我认为我可以通过使约束DEFERRED 来防止.如果我将 INSERT OR REPLACE 包装在事务中，它也不起作用.尽管语句后存在相同的外键，但它总是删除我的额外数据.

But here lies the problem: INSERT OR REPLACE is actually a DELETE followed by an INSERT and the delete part triggers the foreign key ON DELETE which I thought I could prevent by making the constraint DEFERRED. It does not work if I wrap INSERT OR REPLACE in a transaction either. It's always deleting my extra data although the same foreign key exists after the statement.

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

Is it possible to stop ON DELETE to trigger until the INSERT OR REPLACE is finished? Maybe some special transaction mode / pragma ?

推荐答案

如果我将 ON DELETE CASCADE 部分替换为类似以下的触发器似乎可以工作:

It seems work if I replace the ON DELETE CASCADE part by a trigger like:

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 语句触发，到目前为止应该可以解决我的问题.

That trigger is only triggered by a DELETE statement and should solve my problem so far.

^{(OP 在问题中提供的答案)}

jmathew 的其他信息，引用了 文档:

Additional info by jmathew, citing the documentation:

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

When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

这篇关于INSERT OR REPLACE +外键ON DELETE CASCADE工作太好了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！