Documentation Source Text

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.</p>

<p>^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.</p>

<p>The ON CONFLICT clause applies to UNIQUE and NOT NULL constraints (and to
PRIMARY KEY constraints which for the purposes of this section are the same
as UNIQUE constraints).
The ON CONFLICT algorithm does not
apply to CHECK and FOREIGN KEY constraints.
There are five conflict resolution algorithm choices:
ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE.
^The default conflict resolution algorithm is ABORT.  This
is what they mean:</p>

<dl>
<dt><b>ROLLBACK</b></dt>
<dd><p> ^When an applicable constraint violation occurs, the ROLLBACK
resolution algorithm aborts the current SQL statement with
an SQLITE_CONSTRAINT error and rolls back the current transaction.
^If no transaction is
active (other than the implied transaction that is created on every
command) then the ROLLBACK resolution algorithm works the same as the
ABORT algorithm.</p></dd>

<dt><b>ABORT</b></dt>
<dd><p> ^When an applicable constraint violation occurs, the ABORT
resolution algorithm aborts the current SQL statement
with an SQLITE_CONSTRAIT error and backs out any changes
made by the current SQL statement; but changes caused
by prior SQL statements within the same transaction are preserved and the
transaction remains active.
This is the default behavior and the behavior proscribed the the SQL
standard.</p></dd>

<dt><b>FAIL</b></dt>
<dd><p> ^When an applicable constraint violation occurs, the FAIL
resolution algorithm aborts the current SQL statement with an
SQLITE_CONSTRAINT error.  ^But the FAIL resolution does not
back out prior changes of the SQL statement that failed nor does
it end the transaction.
^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.</p></dd>

(<dt><b>IGNORE</b></dt>
<dd><p> ^When an applicable constraint violation occurs, 
the IGNORE resolution algorithm skips the one row that contains
the constraint violation and continues processing subsequent rows
of the SQL statement as if nothing went wrong.
Other rows before and after the row that
contained the constraint violation are inserted or updated
normally. ^No error is returned when the IGNORE conflict resolution
algorithm is used.</p></dd>

<dt><b>REPLACE</b></dt>
<dd><p> ^When a UNIQUE constraint violation occurs, the REPLACE algorithm
deletes pre-existing rows that are causing the constraint violation
prior to inserting or updating the current row and the command continues 
executing normally.

^If a NOT NULL constraint violation occurs, the REPLACE conflict
resolution replaces the NULL value with
he default value for that column, or if the column has no default
value, then the ABORT algorithm is used.</p>


<p>^When the REPLACE conflict resolution strategy deletes rows in order to
satisfy a constraint, [CREATE TRIGGER | delete triggers] fire if and only if
[recursive_triggers pragma | recursive triggers] are enabled.</p>

<p>^The [sqlite3_update_hook | update hook] is not invoked for rows that
are deleted by the REPLACE conflict resolution stretegy.  ^Nor does
REPLACE increment the [sqlite3_changes | change counter].
The exceptional behaviors defined in this paragraph might change 
in a future release.</p>
</dl>

<p>^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.</p>