1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: ON CONFLICT clause</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>ON CONFLICT clause</h2><h4><a href="syntaxdiagrams.html#conflict-clause">conflict-clause:</a></h4><blockquote> <img alt="syntax diagram conflict-clause" src="images/syntax/conflict-clause.gif"></img> </blockquote>
123
<p>The ON CONFLICT clause is not a separate SQL command. It is a
124
non-standard clause that can appear in many other SQL commands.
125
It is given its own section in this document because it is not
126
part of standard SQL and therefore might not be familiar.</p>
128
<p>The syntax for the ON CONFLICT clause is as shown above for
129
the CREATE TABLE command. For the INSERT and
130
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that
131
the syntax reads more naturally. For example, instead of
132
"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
133
The keywords change but the meaning of the clause is the same
136
<p>The ON CONFLICT clause applies to UNIQUE and NOT NULL
137
constraints (and to PRIMARY KEY constraints which for the purposes
138
of this section are the same thing as UNIQUE constraints).
139
The ON CONFLICT algorithm does not
140
apply to <a href="foreignkeys.html">FOREIGN KEY constraints</a>.
141
There are five conflict resolution algorithm choices:
142
ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE.
143
The default conflict resolution algorithm is ABORT. This
144
is what they mean:</p>
147
<dt><b>ROLLBACK</b></dt>
148
<dd><p> When an applicable constraint violation occurs, the ROLLBACK
149
resolution algorithm aborts the current SQL statement with
150
an SQLITE_CONSTRAINT error and rolls back the current transaction.
152
active (other than the implied transaction that is created on every
153
command) then the ROLLBACK resolution algorithm works the same as the
154
ABORT algorithm.</p></dd>
156
<dt><b>ABORT</b></dt>
157
<dd><p> When an applicable constraint violation occurs, the ABORT
158
resolution algorithm aborts the current SQL statement
159
with an SQLITE_CONSTRAIT error and backs out any changes
160
made by the current SQL statement; but changes caused
161
by prior SQL statements within the same transaction are preserved and the
162
transaction remains active.
163
This is the default behavior and the behavior proscribed the SQL
167
<dd><p> When an applicable constraint violation occurs, the FAIL
168
resolution algorithm aborts the current SQL statement with an
169
SQLITE_CONSTRAINT error. But the FAIL resolution does not
170
back out prior changes of the SQL statement that failed nor does
171
it end the transaction.
172
For example, if an UPDATE
173
statement encountered a constraint violation on the 100th row that
174
it attempts to update, then the first 99 row changes are preserved
175
but changes to rows 100 and beyond never occur.</p></dd>
177
<dt><b>IGNORE</b></dt>
178
<dd><p> When an applicable constraint violation occurs,
179
the IGNORE resolution algorithm skips the one row that contains
180
the constraint violation and continues processing subsequent rows
181
of the SQL statement as if nothing went wrong.
182
Other rows before and after the row that
183
contained the constraint violation are inserted or updated
184
normally. No error is returned when the IGNORE conflict resolution
185
algorithm is used.</p></dd>
187
<dt><b>REPLACE</b></dt>
188
<dd><p> When a UNIQUE constraint violation occurs, the REPLACE algorithm
189
deletes pre-existing rows that are causing the constraint violation
190
prior to inserting or updating the current row and the command continues
192
If a NOT NULL constraint violation occurs, the REPLACE conflict
193
resolution replaces the NULL value with
194
he default value for that column, or if the column has no default
195
value, then the ABORT algorithm is used.
196
If a CHECK constraint violation occurs, the REPLACE conflict resolution
197
algorithm always works like ABORT.</p>
199
<p>When the REPLACE conflict resolution strategy deletes rows in order to
200
satisfy a constraint, <a href="lang_createtrigger.html">delete triggers</a> fire if and only if
201
<a href="pragma.html#pragma_recursive_triggers">recursive triggers</a> are enabled.</p>
203
<p>The <a href="c3ref/update_hook.html">update hook</a> is not invoked for rows that
204
are deleted by the REPLACE conflict resolution strategy. Nor does
205
REPLACE increment the <a href="c3ref/changes.html">change counter</a>.
206
The exceptional behaviors defined in this paragraph might change
207
in a future release.</p>
210
<p>The algorithm specified in the OR clause of an INSERT or UPDATE
211
overrides any algorithm specified in a CREATE TABLE.
212
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
added
removed
lang_conflict.html
