INSERT OR REPLACE + foreign key ON DELETE CASCADE working too good

Question

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.

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.

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

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

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).

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 */
   )

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.

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.

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

score 4 · Accepted Answer · edited May 23 '17 at 12:30

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;

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

^{(Answer provided by the OP in the question)}

Additional info by jmathew, citing the documentation:

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.

score 2 · Answer 2 · answered Nov 28 '17 at 03:03

Assuming you only have a single foreign key relationship on a primary key in your referenced table (as you do in your example), this proved to be a fairly painless solution for me.

Simply disable foreign key checks, run the replace query, then enable foreign keys again.

If the replace query is the only query that runs while foreign keys are disabled, you can be assured that no foreign keys will be fouled up. If you are inserting a new row, nothing will have had a chance to be linked to it yet and if you are replacing a row, the existing row will not be removed or have its primary key changed by the query so the constraint will still hold once foreign keys are re-enabled.

SQLlite code looks something like this:

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

score 0 · Answer 3 · answered Aug 03 '20 at 08:57

What about the reasons of such behavior, there's an explanation from PostgreSQL team:

Yeah, this is per SQL spec as far as we can tell. Constraint checks can be deferred till end of transaction, but "referential actions" are not deferrable. They always happen during the triggering statement. For instance SQL99 describes the result of a cascade deletion as being that the referencing row is "marked for deletion" immediately, and then

All rows that are marked for deletion are effectively deleted at the end of the SQL-statement, prior to the checking of any integrity constraints.

INSERT OR REPLACE + foreign key ON DELETE CASCADE working too good

3 Answers3

Linked