Deal with a conflict
Synopsis:
ON CONFLICT { ROLLBACK | ABORT | FAIL | IGNORE | REPLACE }
Description:
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 standard SQL and therefore might not be familiar.
The syntax for the ON CONFLICT clause is as shown above for the
CREATE TABLE command. For the INSERT and
UPDATE commands, the keywords ON CONFLICT
are replaced by OR, to make the syntax seem more natural. For
example, instead of INSERT ON CONFLICT IGNORE we have
INSERT OR IGNORE. The keywords change, but the meaning of the
clause is the same either way.
The ON CONFLICT clause specifies an algorithm used to resolve
constraint conflicts:
- ROLLBACK
- When a constraint violation occurs, an immediate ROLLBACK
occurs, thus ending the current transaction, and the command aborts
with a return code of SQLITE_CONSTRAINT. If no transaction is
active (other than the implied transaction that is created on every
command) then this algorithm works the same as ABORT.
- ABORT
- When a constraint violation occurs, the command backs out
any prior changes it might have made and aborts with a return code
of SQLITE_CONSTRAINT. But no ROLLBACK is executed, so changes
from prior commands within the same transaction
are preserved. This is the default behavior.
- FAIL
- When a constraint violation occurs, the command aborts with a
return code of SQLITE_CONSTRAINT. 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.
- IGNORE
- When a constraint violation occurs, the one row that contains
the constraint violation is not inserted or changed. But 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.
- REPLACE
- 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 insertion 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 doesn't invoke delete triggers on those
rows. This may change in a future release.
The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.