Documentation Source Text

Check-in [90a2cca5d0]
Login

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

Overview
Comment:Clarify the ON CONFLICT clause documentation to make clear that FK violations always work as ABORT or ROLLBACK, never FAIL, IGNORE, or REPLACE.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 90a2cca5d030e1fe9db2f5cc35fa060b6747c177f2d43f9bb67b3053162d7313
User & Date: drh 2019-05-08 16:53:14.108
Context
2019-05-08
17:26
Document the fact that using PRAGMA case_sensitive_like in a database that uses LIKE operators in the schema can lead to problems. (check-in: bc4c1a7626 user: drh tags: trunk)
16:53
Clarify the ON CONFLICT clause documentation to make clear that FK violations always work as ABORT or ROLLBACK, never FAIL, IGNORE, or REPLACE. (check-in: 90a2cca5d0 user: drh tags: trunk)
2019-05-02
19:03
Use freopen() rather than assigning to "stdin" in althttpd.c. (check-in: 229fda6dfb user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
3643
3644
3645
3646
3647
3648
3649
3650




3651
3652
3653
3654
3655
3656
3657
3658
3659

3660



3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
resolution algorithm aborts the current SQL statement with an
SQLITE_CONSTRAINT error.  ^But the FAIL resolution does not
back out prior changes of the SQL statement that failed nor does
it end the transaction.
^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.</p></dd>





<dt><b>IGNORE</b></dt>
<dd><p> ^When an applicable constraint violation occurs, 
the IGNORE resolution algorithm skips the one row that contains
the constraint violation and continues processing subsequent rows
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
are deleted by the REPLACE conflict resolution strategy.  ^Nor does







|
>
>
>
>








|
>
|
>
>
>











|
|







3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
resolution algorithm aborts the current SQL statement with an
SQLITE_CONSTRAINT error.  ^But the FAIL resolution does not
back out prior changes of the SQL statement that failed nor does
it end the transaction.
^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.</p>

<p>The FAIL behavior only works for uniqueness, NOT NULL, and CHECK 
constraints.  A [foreign key constraint] violation causes an ABORT.
</p></dd>

<dt><b>IGNORE</b></dt>
<dd><p> ^When an applicable constraint violation occurs, 
the IGNORE resolution algorithm skips the one row that contains
the constraint violation and continues processing subsequent rows
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 for uniqueness, NOT NULL, and
UNIQUE constraint errors when the IGNORE conflict resolution
algorithm is used.  ^However, the IGNORE conflict resolution
algorithm works like ABORT for [foreign key constraint] errors.
</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] or [foreign key constraint] violation occurs, 
the REPLACE conflict resolution algorithm 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 strategy.  ^Nor does