Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

This does look very useful - I’ve often thought of the INSERT/EPLACE style but the implicit DELETE [when REPLACE occurs] was a barrier - it causes [as I understand it] CASCADE DELETE to be in effect. It seems that this UPSERT style does not cause that effect. > On Jan 8, 2020, at 5:22 AM, Richar

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 1/7/20, Michael Kappert wrote: > > If I understand correctly, the upsert should behave like UPDATE in the > examples above, but it behaves like a DELETE followed by INSERT instead? > REPLACE and UPSERT are different things. See https://www.sqlite.org/lang_conflict.html for information about R

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 2020/01/08 1:10 PM, Simon Slavin wrote: I advise you avoid the idea of UPSERT when dealing with SQLite (or better still, all SQL). It is rarely implemented as a single operation, and you can get unexpected results with triggers and foreign key children. I advise you to avoid the idea of d

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

I advise you avoid the idea of UPSERT when dealing with SQLite (or better still, all SQL). It is rarely implemented as a single operation, and you can get unexpected results with triggers and foreign key children. Think of your operation as a combinations of INSERT, DELETE and UPDATE and you w

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert (Docs Update?)

On Tuesday, 7 January, 2020 17:05, Simon Slavin wrote: >On 8 Jan 2020, at 12:00am, Michael Kappert wrote: >> REPLACE INTO >REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume >that the command will do either an INSERT or a REPLACE. >See the notes about REPLACE on this page:

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 2020/01/08 2:00 AM, Michael Kappert wrote: -- Modify one parent entry of foreign key fk_t1_id REPLACE INTO T1 (ID, NAME) VALUES ('A', 'line 1-new'); If I understand correctly, the upsert should behave like UPDATE in the

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 8.01.20 01:04, Simon Slavin wrote: > On 8 Jan 2020, at 12:00am, Michael Kappert wrote: > >> REPLACE INTO > > REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume that > the command will do either an INSERT or a REPLACE. > > See the notes about REPLACE on this page: > >

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

On 8 Jan 2020, at 12:00am, Michael Kappert wrote: > REPLACE INTO REPLACE INTO is an alias for INSERT OR REPLACE. So you should assume that the command will do either an INSERT or a REPLACE. See the notes about REPLACE on this page: Is the behaviour yo

[sqlite] Cascaded delete unexpectedly triggered by upsert

Hi, as this is my first posting to this list, let me first say thank you for providing such a great library. It's fun to work with it. I'm intending to use SQLite in a game engine. I've encountered some unexpected behavior of foreign keys w.r.t an upsert: When an entry of the parent table is 'tou