SQLite syntax Commands

[Decid] INTEGER (+) not NULL PRIMARY KEY on CONFLICT FAIL,

The on conflict clause is not a standalone SQL command. This is a non-standard clause that can appear in many other SQL commands. Since it is not a standard SQL language, it is described here separately.

The syntax of the on conflict clause is shown in the CREATE TABLE command as above. For insert and update, the keyword "on CONFLICT" is replaced by "OR", which makes the syntax seem natural. For example, do not write "insert on CONFLICT IGNORE" but "Insert OR IGNORE". The two mean the same thing.

The on conflict clause defines an algorithm for resolving constraint conflicts. There are five options: ROLLBACK, Abort, FAIL, IGNORE, and replace. The default scheme is ABORT. The options have the following meanings:

ROLLBACK

When a constraint violation occurs, immediately rollback, which ends the current transaction, the command aborts and returns the Sqlite_constraint code. The algorithm is the same as abort if there are currently no active transactions (other than the default transaction created by each command).

ABORT

When a constraint conflict occurs, the command retracts the change that has been caused and aborts the return sqlite_constraint. However, because the rollback is not executed, the changes made in the preceding command will be retained. This behavior is used by default.

FAIL

When a constraint violation occurs, the command aborts the return sqlite_constraint. However, all changes before the conflict will be retained. For example, if an UPDATE statement encounters conflict 100th in 100 rows, the first 99 rows will be changed and the changes to 100 or later will not occur.

IGNORE

When a constraint conflict occurs, the row in conflict will not be inserted or changed. But the command will be executed as usual. Rows before or after the conflict line are inserted and changed normally, and no error message is returned.

REPLACE

When a unique constraint violation occurs, the row that causes the conflict is deleted before the conflicting row is changed or inserted. In this way, changes and insertions are always performed. The command executes as usual and does not return an error message. When a NOT NULL constraint conflict occurs, the null value that causes the conflict is replaced by the field default value. If the field is not saved, execute the abort algorithm.

When a conflict response policy deletes a row to satisfy a constraint, it does not invoke a delete trigger. However, this feature may be changed in the new version.

The algorithm defined by the OR clause of the INSERT or update overrides the definition of CREATE table. The abort algorithm is used by default when no algorithm is defined.