SQL As Understood By SQLite
ON CONFLICT clause
| 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 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 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.
撤销当错误发生时会立即调用ROLLBACK事件，中止当前的事件，返回“SQLITE_CONSTRAINT”。如果ROLLBACK没有可用事件（other than the implied transaction that is created on every command这句怎么翻译？），它等效于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.
- 中止
-
当错误发生，撤销先前所有可能发生的更改，返回SQLITE_CONSTRAINT，中止函数。但是没有ROLLBACK被执行，因此先前命令中有相同表达式的更改仍被保存（这句话是什么意思？）。这是默认的操作。
- 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.
- 失败
-
当错误发生，返回SQLITE_CONSTRAINT，中止函数。但是命令先前所作的更改将被保存并且不会被撤销。比如：如果一个UPDATE语句在第100行遇到错误，那么前99行的改变将被保存，100行与100行之后的的改变将不会发生。
- 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 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.
- 替换
-
当唯一（UNIQUE是这样翻译吗？）的错误发生时，引起错误的行将被正确的行取代。语句继续正常执行，不返回错误。如果是“NOT NULL”错误（这是什么错误？），NULL的值将被默认值替代。如果列没有默认值，将调用ABORT。
当是奇怪的错误时会删除错误行（这是为了保证安全），但对于这些行没有触发delete事件。将来的版本可能改变这些特性。
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.
This page last modified on 2005/05/10 16:11:41