Syntax:
ON CONFLICT conflict-algorithm conflict-algorithm: ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
The ON CONFLICT clause is not a separate SQL command. It is a non-standard clause that can appear in many other SQL commands. It is given its own section in this document because it is not part of the standard SQL and therefore users might not be familiar with it.
The algorithm specified in the OR clause of an INSERT or UPDATE statement overrides any algorithm specified in the CREATE TABLE statement. If no algorithm is specified anywhere, the ABORT algorithm is used.
The ON CONFLICT clause specifies the algorithm used to resolve constraint conflicts. The available choices are: ROLLBACK | ABORT, FAIL, IGNORE and REPLACE. The default algorithm is ABORT. The behavior of these algorithms is explained below:
When a constraint violation occurs, an immediate rollback is executed (i.e. the results of all prior commands in the same transaction are reverted), ending the current transaction, and the command aborts. If no transaction is active (other than the implied transaction that is created on every command) then this algorithm works the same as ABORT.
When a constraint violation occurs, the command backs out any prior changes it might have made and aborts. But no rollback is executed, so changes from prior commands within the same transaction are preserved. This is the default behavior.
When a constraint violation occurs, the command aborts. But any changes to the database that the command made prior to encountering the constraint violation are preserved and are not backed out. For example, if an UPDATE statement encountered a constraint violation on the 100th row that it attempts to update, then the first 99 row changes are preserved but changes to rows 100 and beyond never occur.
When a constraint violation occurs, the one row that contains the constraint violation is not inserted or changed and the command continues executing normally. Other rows before and after the row that contained the constraint violation continue to be inserted or updated normally. No error is returned.
When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs. The command continues executing normally. No error is returned. If a NOT NULL constraint violation occurs, the NULL value is replaced by the default value for that column. If the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs then the IGNORE algorithm is used.
When this conflict resolution strategy deletes rows in order to satisfy a constraint, it does not invoke delete triggers on those rows. This behavior might change in a future release.