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 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 remains the same.
The
ON CONFLICT clause specifies an algorithm for resolving
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. 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 violation are removed
prior to inserting or updating the current row. Thus, the insertion or
update always occurs and 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.
Note: 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 an
INSERT or UPDATE command overrides any
algorithm specified in a CREATE TABLE. If no algorithm is
specified anywhere, the ABORT algorithm is used.