HTML DEVELOPER’S GUIDE FOR ADOBE AIR
SQL support in local databases
Last updated 9/28/2011
The first form of the ON CONFLICT clause, using the keywords ON CONFLICT, is used in a CREATE TABLE
statement. For an INSERT or UPDATE statement, the second form is used, with ON CONFLICT replaced by OR to
make the syntax seem more natural. For example, instead of INSERT ON CONFLICT IGNORE, the statement becomes
INSERT OR IGNORE. Although the keywords are different, the meaning of the clause is the same in either form.
The ON CONFLICT clause specifies the algorithm that is used to resolve constraint conflicts. The five algorithms are
ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default algorithm is ABORT. The following is an
explanation of the five conflict algorithms:
When a constraint violation occurs, an immediate ROLLBACK occurs, ending the current transaction.
The command aborts and the SQLStatement instance dispatches an error event. If no transaction is active (other than
the implied transaction that is created on every command) then this algorithm works the same as ABORT.
When a constraint violation occurs, the command backs out any prior changes it might have made and the
SQLStatement instance dispatches an error event. No ROLLBACK is executed, so changes from prior commands
within a transaction are preserved. ABORT is the default behavior.
When a constraint violation occurs, the command aborts and the SQLStatement dispatches an error event.
However, any changes to the database that the statement made before encountering the constraint violation are
preserved and are not backed out. For example, if an UPDATE statement encounters 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
When a constraint violation occurs, the one row that contains the constraint violation is not inserted or
changed. Aside from this row being ignored, 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 a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation
are removed before inserting or updating the current row. Consequently, the insert or update always occurs, and 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. If a CHECK constraint violation occurs then the IGNORE 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.
The algorithm specified in the OR clause of an INSERT or UPDATE statement overrides any algorithm specified in a
CREATE TABLE statement. If no algorithm is specified in the CREATE TABLE statement or the executing INSERT
or UPDATE statement, the ABORT algorithm is used.
The REINDEX command is used to delete and re-create one or more indices. This command is useful when the
definition of a collation sequence has changed.
sql-statement ::= REINDEX collation-name
sql-statement ::= REINDEX [database-name .] ( table-name | index-name )
In the first form, all indices in all attached databases that use the named collation sequence are recreated. In the second
form, when a table-name is specified, all indices associated with the table are rebuilt. If an index-name is given, only
the specified index is deleted and recreated.
Comments aren't SQL commands, but they can occur in SQL queries. They are treated as white space by the runtime.
They can begin anywhere white space can be found, including inside expressions that span multiple lines.