Support onDuplicateKey

[oddanderson]

There are a bunch of issues using onConflict: .replace in conjunction with foreign keys.

In SQL, ON CONFLICT REPLACE deletes the underlying row and subsequently re-inserts the row. This becomes an issue when you the row in question is referenced by the foreign key constraint, ON DELETE CASCADE.

What will happen is that the deletion issued by the conflict will cascade, when you don't want to. It's for this reason that many people use ON DUPLICATE KEY UPDATE in favor of ON CONFLICT REPLACE

I do acknowledge it's considerably more challenging, because the sql insertion query has to explicitly set the variables and values in the UPDATE command, but is likely quite similar to generating the query with the existing update support.

[groue]

Yes. Some people call this UPSERT, introduced in SQLite 3.24.0: https://www.sqlite.org/lang_UPSERT.html

It would be very cool to support this feature indeed.