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 specifies an algorithm used to resolve




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


<dt><b>ABORT</b></dt>
<dd><p>^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.</p></dd>


<dt><b>FAIL</b></dt>
<dd><p>^When a constraint violation occurs, the command aborts with a
return code SQLITE_CONSTRAINT.  ^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.</p></dd>

<dt><b>IGNORE</b></dt>
<dd><p>^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 when the IGNORE conflict resolution
algorithm is used.</p></dd>

<dt><b>REPLACE</b></dt>
<dd><p>^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 following REPLACE.  
^No error is returned by the REPLACE conflict resolution.
^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.</p>

<p>^When this conflict resolution strategy deletes rows in order to
satisfy a constraint, [CREATE TRIGGER | delete triggers] only fire 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 an OR REPLACE resolution.  ^Nor is the 
[sqlite3_changes | change counter] incremented.
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>

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>