Foreign keys enforcement can get tricky depending on the enforcement policy,
transactions, and a lot of things. I don’t have enough experience to comment
on that fully.
I will say this, however, because it is a common mistake with a lot of
different aspects of database behavior:
“Insert or replace” is NOT “insert or update.”
Insert always inserts a new row. The only question is if it deletes
conflicting rows (yes, plural) first.
With that in mind, the FK concept gets a little fuzzy. It is a new row that
just happens to have the same ID as an old row. Does that mean the FK in B
should still reference it? I’d say no, because it is a brand new row… a
different entity. If you want B to keep referencing the existing row, then
update the row that is already there, don’t delete it and insert a new row on
top of it. I’d look into deferrable constraints to see if the behavior you’re
looking for is supported. Hopefully others can comment more on that.
-j
> On Mar 9, 2018, at 1:11 PM, John Found <[email protected]> wrote:
>
>
> I have two tables with foreign constraint:
>
> create table A ( id primary key not null, single_data );
> create table B ( aid references A(id) on delete cascade, multi_data);
>
> Now I am periodically inserting data in A and B with the following queries:
>
> insert or replace into A values (?1, ?2);
> insert into B values (?1, ?2);
>
> Unfortunately, after replacing some row in A, all previously inserted rows in
> B got deleted, even if the value of ID does not changes. Here SQLite works
> exactly as it first deletes the conflicting row from A and then inserting new.
>
> Now, if I define the table B without "on delete":
>
> create table B ( aid references A(id), multi_data);
>
> "insert or replace" succeed without deleting the old rows from B.
>
> In my opinion this behaviour is not consistent. The consistent solutions IMHO
> are two:
>
> 1. "insert or replace" succeed both in the first and in the second case
> without deleting rows from B,
>
> 2. "insert or delete" succeed in the first case, deleting all constrained
> rows from B and fails in the second case with "FOREIGN KEY constraint failed".
>
> The first case IMHO is more intuitive and natural. At least this was my
> expectation when writing the code.
>
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found <[email protected]>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
