INSERT OR REPLACE and foreign keys

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

INSERT OR REPLACE and foreign keys

Roman Fleysher
Dear SQLiters,

I am using INSERT OR REPLACE to update a table which holds a column which servers as a foreign key. But I noticed a strange behavior: If the parent record existed, then replace mechanism replaces it, but the records from children tables are deleted. The foreign key is set up to cascade on delete, as I think it should. So it seems that "replace" is implemented as "delete then insert" rather than "update". Is that normal and expected? Am I doing something wrong?

Thank you,

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT OR REPLACE and foreign keys

Shawn Wagner
That's normal. It deletes the conflicting row and inserts a new one.

On Tue, Oct 23, 2018, 10:58 AM Roman Fleysher <
[hidden email]> wrote:

> Dear SQLiters,
>
> I am using INSERT OR REPLACE to update a table which holds a column which
> servers as a foreign key. But I noticed a strange behavior: If the parent
> record existed, then replace mechanism replaces it, but the records from
> children tables are deleted. The foreign key is set up to cascade on
> delete, as I think it should. So it seems that "replace" is implemented as
> "delete then insert" rather than "update". Is that normal and expected? Am
> I doing something wrong?
>
> Thank you,
>
> Roman
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT OR REPLACE and foreign keys

Olivier Mascia
In reply to this post by Roman Fleysher
> Le 23 oct. 2018 à 19:52, Roman Fleysher <[hidden email]> a écrit :
>
> I am using INSERT OR REPLACE ... it seems that "replace" is implemented as "delete then insert" rather than "update". Is that normal and expected? Am I doing something wrong?

Normal and expected.  Check https://www.sqlite.org/lang_insert.html. The OR REPLACE is a kind of exception handling mechanism.

What you are looking for is the upsert clause for your insert statement: https://www.sqlite.org/syntax/upsert-clause.html.

Some other engines might have an INSERT OR UPDATE (not REPLACE) and you have lead to think the OR REPLACE implemented by SQLite is that feature. It is not. The more general ON CONFLICT ... DO ... is what you're after.


--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] INSERT OR REPLACE and foreign keys

Hick Gunter
In reply to this post by Roman Fleysher
This is the expected and documented behaviour. Maybe you are looking for UPSERT?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Roman Fleysher
Gesendet: Dienstag, 23. Oktober 2018 19:53
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [EXTERNAL] [sqlite] INSERT OR REPLACE and foreign keys

Dear SQLiters,

I am using INSERT OR REPLACE to update a table which holds a column which servers as a foreign key. But I noticed a strange behavior: If the parent record existed, then replace mechanism replaces it, but the records from children tables are deleted. The foreign key is set up to cascade on delete, as I think it should. So it seems that "replace" is implemented as "delete then insert" rather than "update". Is that normal and expected? Am I doing something wrong?

Thank you,

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users