SQLite Forum

How to not to overwrite a row if the key exists?
Login
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](https://sqlite.org/lang_conflict.html)

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.