SQL Maestro Group / Products / SQLite / SQLite Code Factory / Documentation

SQLite Code Factory online Help

conflict-clause ::=	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 standard SQL and therefore might not be familiar.

The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE, CREATE INDEX, and BEGIN TRANSACTION commands. For the COPY, INSERT, and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make the syntax seem more natural. But the meaning of the clause is the same either way.

The ON CONFLICT clause specifies an algorithm used to resolve constraint conflicts. There are five choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default algorithm is ABORT. This is what they mean:

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

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 row that is causing the constraint violation is 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.

The conflict resolution algorithm can be specified in three places, in order from lowest to highest precedence:

1.	On a BEGIN TRANSACTION command.

2.	On individual constraints within a CREATE TABLE or CREATE INDEX statement.

3.	In the OR clause of a COPY, INSERT, or UPDATE command.

The algorithm specified in the OR clause of a COPY, INSERT, or UPDATE overrides any algorithm specified by a CREATE TABLE or CREATE INDEX. The algorithm specified within a CREATE TABLE or CREATE INDEX will, in turn, override the algorithm specified by a BEGIN TRANSACTION command. If no algorithm is specified anywhere, the ABORT algorithm is used.

Return to chapter overview