Documentation Source Text

Check-in [445adeb211]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Clarifications to the ON CONFLICT documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 445adeb2111570097ebd704ac9ff10400cc2d59a
User & Date: drh 2014-08-15 00:49:36.570
Context
2014-08-15
13:36
Clarification and amplification of AUTOINCREMENT. (check-in: 60668454be user: drh tags: trunk)
00:49
Clarifications to the ON CONFLICT documentation. (check-in: 445adeb211 user: drh tags: trunk)
2014-08-14
14:39
Change an "is" to a "was" in the news. (check-in: 7ca803de2b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
the CREATE TABLE command.  ^For the INSERT and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that
the syntax reads more naturally.  For example, instead of
"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
The keywords change but the meaning of the clause is the same
either way.</p>

<p>The ON CONFLICT clause applies to UNIQUE and NOT NULL
constraints (and to PRIMARY KEY constraints which for the purposes 
of this section are the same thing as UNIQUE constraints).
The ON CONFLICT algorithm does not
apply to [FOREIGN KEY constraints].
There are five conflict resolution algorithm choices:
ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE.
^The default conflict resolution algorithm is ABORT.  This
is what they mean:</p>








|
<
|







3135
3136
3137
3138
3139
3140
3141
3142

3143
3144
3145
3146
3147
3148
3149
3150
the CREATE TABLE command.  ^For the INSERT and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that
the syntax reads more naturally.  For example, instead of
"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
The keywords change but the meaning of the clause is the same
either way.</p>

<p>The ON CONFLICT clause applies to [UNIQUE], [NOT NULL],

[CHECK], and [PRIMARY KEY] constraints.
The ON CONFLICT algorithm does not
apply to [FOREIGN KEY constraints].
There are five conflict resolution algorithm choices:
ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE.
^The default conflict resolution algorithm is ABORT.  This
is what they mean:</p>

3187
3188
3189
3190
3191
3192
3193

3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
of the SQL statement as if nothing went wrong.
Other rows before and after the row that
contained the constraint violation are inserted or updated
normally. ^No error is returned when the IGNORE conflict resolution
algorithm is used.</p></dd>

<dt><b>REPLACE</b></dt>

<dd><p> ^When a UNIQUE constraint violation occurs, the REPLACE algorithm
deletes pre-existing rows that are causing the constraint violation
prior to inserting or updating the current row and the command continues 
executing normally.
^If a NOT NULL constraint violation occurs, the REPLACE conflict
resolution replaces the NULL value with
the default value for that column, or if the column has no default
value, then the ABORT algorithm is used.
^If a CHECK constraint violation occurs, the REPLACE conflict resolution
algorithm always works like ABORT.</p>

<p>^When the REPLACE conflict resolution strategy deletes rows in order to
satisfy a constraint, [CREATE TRIGGER | delete triggers] fire if and only if
[recursive_triggers pragma | recursive triggers] are enabled.</p>

<p>^The [sqlite3_update_hook | update hook] is not invoked for rows that







>
|



|



|







3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
of the SQL statement as if nothing went wrong.
Other rows before and after the row that
contained the constraint violation are inserted or updated
normally. ^No error is returned when the IGNORE conflict resolution
algorithm is used.</p></dd>

<dt><b>REPLACE</b></dt>
<dd><p> ^When a [UNIQUE] or [PRIMARY KEY] constraint violation occurs, 
the REPLACE algorithm
deletes pre-existing rows that are causing the constraint violation
prior to inserting or updating the current row and the command continues 
executing normally.
^If a [NOT NULL] constraint violation occurs, the REPLACE conflict
resolution replaces the NULL value with
the default value for that column, or if the column has no default
value, then the ABORT algorithm is used.
^If a [CHECK] constraint violation occurs, the REPLACE conflict resolution
algorithm always works like ABORT.</p>

<p>^When the REPLACE conflict resolution strategy deletes rows in order to
satisfy a constraint, [CREATE TRIGGER | delete triggers] fire if and only if
[recursive_triggers pragma | recursive triggers] are enabled.</p>

<p>^The [sqlite3_update_hook | update hook] is not invoked for rows that