How to not to overwrite a row if the key exists?

(1) By 6kEs4Majrd on 2020-06-04 23:09:13 [link] [source]

I use the following sqlite3 code (in python apsw) to overwrite or insert a row in a database.

c.execute('CREATE TABLE IF NOT EXISTS sqlar(name TEXT PRIMARY KEY, mode INT, mtime INT, sz INT, data BLOB)')
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', [name, 0, t, len_data, zlib_data])

If I want to only insert the row when the key name does not exist, what is the correct way to do it? Thanks.

(2) By Keith Medcalf (kmedcalf) on 2020-06-05 03:36:07 in reply to 1 [link] [source]

Use INSERT rather than REPLACE?

(3) By Warren Young (wyoung) on 2020-06-05 05:56:52 in reply to 2 [link] [source]

Either that or the "ON CONFLICT DO NOTHING" flavor of upsert.

(4.1) By Keith Medcalf (kmedcalf) on 2020-06-05 06:58:38 edited from 4.0 in reply to 3 [link] [source]

Yes, you can apparently specify something ludicrous like:

REPLACE INTO x VALUES (...) ON CONFLICT DO NOTHING;
which is of course an alternate spelling for
INSERT or REPLACE INTO x VALUES (...) ON CONFLICT DO NOTHING;

and indeed the "last seen" conflict resolution method (ON CONFLICT) overrides the prior resolution method (or REPLACE). Though I don't really understand why you would do that -- it seems rather foolish to me.

I can see perhaps favouring the use of: INSERT INTO x VALUES (...) ON CONFLICT DO NOTHING;
over the more succinct
INSERT or IGNORE INTO x VALUES (...);
because one prefers using ON CONFLICT resolution clauses rather than the "or IGNORE" clause, however the ON CONFLICT clause does not (and cannot) equate to the "or REPLACE"/"or ROLLBACK"/"or ABORT"/"or FAIL" clauses.

That is, "INSERT or REPLACE" is not and never will be equivalent to any INSERT ... ON CONFLICT UPDATE ... because they behave entirely differently. One does an UPDATE when a matching conflict is found, the other DELETEs conflicting rows and does an insert, which are entirely different things. In some narrowly defined and specific implementations with very particular database schema constructions they made achieve the same result, but that is only by happenstance and sheer luck.

(5) By 6kEs4Majrd on 2020-06-05 13:03:57 in reply to 4.1 [link] [source]

This becomes confusing.

"INSERT or IGNORE INTO" is on the diagram at the top of the following webpage. But I don't see it is explained in the text on that webpage. What does it mean?

https://www.sqlite.org/lang_insert.html

I don't want to insert a record if the name of the try-to-be-inserted record exists and insert the record if the try-to-be-inserted record does not exist. Is INSERT or IGNORE INTO x VALUES (...); the best to use for this case? Thanks.

(6) By Ryan Smith (cuz) on 2020-06-05 14:04:05 in reply to 5 [source]

I've seen now quite a few questions in this thread, so thought it prudent to elaborate on conflict resolution. This may be boring to seasoned users, so consider TLDR warning issued.

The "OR IGNORE" part of the "INSERT OR IGNORE ..." clause is a conflict handling directive, i.e. it tells the engine how to handle conflicts.

A conflict is when you are trying to add/insert/update something that causes a constraint to fail.

A constraint is a clear rule you've added to the database.

An example of a real life constraint is that any human's Age can never be less than Zero. In a database it can easily be less than Zero, unless you place that constraint rule on the column by adding [... CHECK Age >= 0], for instance.

A unique key is a constraint too, so if you declare a column as UNIQUE or PRIMARY KEY or such, you say "This column can never contain duplicate values", so when you try to insert a row with the exact same value for that column than an existing row, a constraint failure happens, which is called a "conflict" and which could be handled in several different ways.

One way of handling that conflict is to say "If I try to add a row that causes a conflict[1], please ignore my attempt and continue as if nothing changed" - aka, "IGNORE it".

The way to say that is: "INSERT OR IGNORE INTO ..."

Let's say you have an intrinsic safety application and you may wish to not only ignore the conflict, but report it by failing and stopping the entire transaction (and return a FAIL response in the API), then you would say: "INSERT OR FAIL ..."

Another is to say you immediately want to cancel a transaction if any such conflict happens, which would be "INSERT OR ABORT" or perhaps "INSERT OR ROLLBACK ...".

See: ON CONFLICT

Lately, a new addition to SQLite allows us to go one further and say, if the row I'm trying to insert does conflict, please don't insert it anymore, but at least update columns c1, c2, ... etc. The format for this is: INSERT INTO .... ON CONFLICT UPDATE SET c1 = v1,... etc[2], but you can also end that with "... ON CONFLICT IGNORE" which achieves much the same as the other IGNORE clause.

Things to note:

[1] INSERT OR IGNORE ... will ignore the insert based on ANY conflict, not just the primary key. If you have CHECK constraints or perhaps another UNIQUE column, conflicts in them will ALSO activate the IGNORE (or whatever conflict resolution you've specified).
[2] An updated column cannot still violate the constraint (i.e. cause a conflict) else the statement will still be aborted.

Hope that's a bit more clear.

(7) By 6kEs4Majrd on 2020-06-05 14:43:42 in reply to 6 [link] [source]

This is helpful. In my example code, the only constraint is on the primary key column name.

In this specific case,

INSERT or IGNORE INTO x VALUES (...);

the above should be better than the following?

REPLACE INTO x VALUES (...) ON CONFLICT DO NOTHING; INSERT or REPLACE INTO x VALUES (...) ON CONFLICT DO NOTHING;

(At least, the first command is shorter to type:))

(8) By Keith Medcalf (kmedcalf) on 2020-06-05 15:41:05 in reply to 7 [link] [source]

First of all, you do not necessarily need multiple conflict resolution methods.

insert or ignore ... will insert the row(s) and ignore rows which violation any constraint (other than foreign key constraints).

insert ... on conflict do nothing will insert the row(s) and ignore row(s) which violate uniqueness constraints only. Violation of any other constraint (NOT NULL, CHECK, FOREIGN KEY) will ABORT the statement.

They can be combined.
insert or rollback ... on conflict do nothing
would insert the row(s) if there was no constraint violations, ignore rows that had "unique" constraint violations, and for other constraint violations (NOT NULL and CHECK) would rollback the transaction.