Documentation Source Text

Check-in [5395a69f2f]
Login

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

Overview
Comment:ON CONFLICT does in fact work with CHECK constraints. Fix the documentation to show this. The docs used to be right, but were broken by the prior check-in.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5395a69f2f5898392302ae5577f4c5aadaf0af29
User & Date: drh 2010-08-03 13:14:16.000
Context
2010-08-04
12:03
Fix a broken hyperlink from lang_conflict.html to foreignkeys.html. (check-in: 5b960cc498 user: drh tags: trunk)
2010-08-03
13:14
ON CONFLICT does in fact work with CHECK constraints. Fix the documentation to show this. The docs used to be right, but were broken by the prior check-in. (check-in: 5395a69f2f user: drh tags: trunk)
02:26
Updates to language documentation. (check-in: c412626afe user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
<p>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.</p>

<p>^The syntax for the ON CONFLICT clause is as shown above for
the CREATE TABLE command.  ^For the INSERT and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make
the syntax seem more natural.  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
as UNIQUE constraints).
The ON CONFLICT algorithm does not
apply to CHECK and 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>

<dl>
<dt><b>ROLLBACK</b></dt>







|
|




|
|
|

|







2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
<p>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.</p>

<p>^The syntax for the ON CONFLICT clause is as shown above for
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, and CHECK
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>

<dl>
<dt><b>ROLLBACK</b></dt>
2458
2459
2460
2461
2462
2463
2464
2465


2466
2467
2468
2469
2470
2471
2472
<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
he default value for that column, or if the column has no default
value, then the ABORT algorithm is used.</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
are deleted by the REPLACE conflict resolution stretegy.  ^Nor does







|
>
>







2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
<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
he 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
are deleted by the REPLACE conflict resolution stretegy.  ^Nor does