Learning + translation of SQL as understood by SQLite -- On conflict clause

SQL as understood by SQLite

[Contents]

On conflict clause

Conflict-clause: =	On conflictConflict-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 your 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.

On conflict (when an error occurs) statements are not separate SQL commands. It may appear in many other SQL statements. It is proposed separately because it is not part of the standard SQL language and is not similar to the standard SQL language.

The syntax for The on conflict clause is as shown abve for the create table and create index 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 keyword is usually displayed in the create table and create index commands. In command copy, insert, and update, we use "or" to replace "on Conflict" to make the syntax more natural.

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:

On conflict defines keywords as constraints. They are: rollback (UNDO), abort (abort), fail (failed), ignore (ignore), and replace (replace)

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.

 

Undo

When an error occurs, the rollback event is called immediately to stop the current event and "sqlite_constraint" is returned ". If rollback has no event available (other than the implied transaction that is created on every command ?), It is equivalent to 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.

Abort

When an error occurs, undo all previous changes that may occur, return sqlite_constraint, and stop the function. However, no rollback is executed, so changes with the same expression in the previous command are still saved (what does this mean ?). This is the default operation.

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.

Failed

When an error occurs, sqlite_constraint is returned to stop the function. However, the previous changes to the command will be saved and will not be undone. For example, if an update statement encounters an error in row 100th, the change in the first 99 rows will be saved, and the change after row 100 and row 100 will not happen.

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.

Ignore

When an error occurs, the row where the error occurs is not added or changed. The subsequent commands will be executed as usual. Everything continues and no error is returned.

Replace

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

When this conflict resolution strategy deletes rows in order to satisfy a constraint, it does not invoke Delete triggers on those rows. But that may change in a future release.

Replace

If it is unique (does unique translate like this ?) When an error occurs, the row that causes the error is replaced by the correct row. The statement continues to run normally without returning an error. If it is a "not null" error (what is this error ?), The null value is replaced by the default value. If the column does not have a default value, abort is called.

When a strange error occurs, the row is deleted (to ensure security), but the delete event is not triggered for these rows. Future versions may change these features.

The algorithm specified in the OR clause of a copy, insert, or update overrides any algorithm specified in a create table or create index. If no algorithm is specified anywhere, the abort algorithm is used.

The default operation is abort.

This page last modified on 16:11:41