1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>The ON CONFLICT Clause</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
The ON CONFLICT Clause
</div>
</div>
<p><b><a href="syntax/conflict-clause.html">conflict-clause:</a></b>
<button id='x1379' onclick='hideorshow("x1379","x1380")'>hide</button></p>
<div id='x1380' class='imgcontainer'>
<div style="max-width:451px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 451.517 205.2">
<circle cx="5" cy="6" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,6 20,10 20,2" style="fill:rgb(0,0,0)"/>
<path d="M9,6L26,6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="70,36 58,41 58,32" style="fill:rgb(0,0,0)"/>
<path d="M32,6 L 39,6 Q 47,6 47,21 L 47,21 Q 47,36 56,36 L 64,36" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M85,51L94,51A15 15 0 0 0 109 36L109,36A15 15 0 0 0 94 21L85,21A15 15 0 0 0 70 36L70,36A15 15 0 0 0 85 51Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="90" y="36" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ON</text>
<polygon points="132,36 121,41 121,32" style="fill:rgb(0,0,0)"/>
<path d="M109,36L126,36" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M147,51L222,51A15 15 0 0 0 237 36L237,36A15 15 0 0 0 222 21L147,21A15 15 0 0 0 132 36L132,36A15 15 0 0 0 147 51Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="184" y="36" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">CONFLICT</text>
<polygon points="273,36 261,41 261,32" style="fill:rgb(0,0,0)"/>
<path d="M237,36L267,36" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M288,51L365,51A15 15 0 0 0 381 36L381,36A15 15 0 0 0 365 21L288,21A15 15 0 0 0 273 36L273,36A15 15 0 0 0 288 51Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="327" y="36" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ROLLBACK</text>
<path d="M288,89L330,89A15 15 0 0 0 345 74L345,74A15 15 0 0 0 330 59L288,59A15 15 0 0 0 273 74L273,74A15 15 0 0 0 288 89Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="309" y="74" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ABORT</text>
<path d="M288,127L310,127A15 15 0 0 0 325 112A15 15 0 0 0 310 97L288,97A15 15 0 0 0 273 112A15 15 0 0 0 288 127Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="299" y="112" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">FAIL</text>
<path d="M288,165L340,165A15 15 0 0 0 355 150A15 15 0 0 0 340 135L288,135A15 15 0 0 0 273 150A15 15 0 0 0 288 165Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="314" y="150" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">IGNORE</text>
<path d="M288,203L352,203A15 15 0 0 0 367 187A15 15 0 0 0 352 172L288,172A15 15 0 0 0 273 187A15 15 0 0 0 288 203Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="320" y="187" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">REPLACE</text>
<polygon points="404,36 392,41 392,32" style="fill:rgb(0,0,0)"/>
<path d="M381,36L398,36" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="442,6 430,10 430,2" style="fill:rgb(0,0,0)"/>
<path d="M404,36 L 411,36 Q 419,36 419,21 L 419,21 Q 419,6 427,6 L 436,6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="445" cy="6" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="225,6 214,10 214,2" style="fill:rgb(0,0,0)"/>
<path d="M9,6L219,6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M225,6L434,6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="273,187 261,192 261,183" style="fill:rgb(0,0,0)"/>
<path d="M237,36 L 244,36 Q 252,36 252,51 L 252,172 Q 252,187 259,187 L 267,187" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="404,187 392,192 392,183" style="fill:rgb(0,0,0)"/>
<path d="M367,187L398,187" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M404,187 L 411,187 Q 419,187 419,172 L 419,36 L 419,21" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="273,74 261,78 261,70" style="fill:rgb(0,0,0)"/>
<path d="M252,59 L 252,66 Q 252,74 259,74 L 267,74" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="404,74 392,78 392,70" style="fill:rgb(0,0,0)"/>
<path d="M345,74L398,74" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M404,74 L 411,74 Q 419,74 419,67 L 419,59" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="273,112 261,116 261,108" style="fill:rgb(0,0,0)"/>
<path d="M252,97 L 252,104 Q 252,112 259,112 L 267,112" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="404,112 392,116 392,108" style="fill:rgb(0,0,0)"/>
<path d="M325,112L398,112" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M404,112 L 411,112 Q 419,112 419,104 L 419,97" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="273,150 261,154 261,145" style="fill:rgb(0,0,0)"/>
<path d="M252,135 L 252,142 Q 252,150 259,150 L 267,150" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="404,150 392,154 392,145" style="fill:rgb(0,0,0)"/>
<path d="M355,150L398,150" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M404,150 L 411,150 Q 419,150 419,142 L 419,135" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<p>The ON CONFLICT clause is a
non-standard extension specific to SQLite
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 ON CONFLICT clause described here has been a part of
SQLite since before version 3.0.0 (2004-06-18). The phrase
"ON CONFLICT" is also part of <a href="lang_upsert.html">UPSERT</a>, which is an extension
to <a href="lang_insert.html">INSERT</a> added in version 3.24.0 (2018-06-04). Do not
confuse these two separate uses of the "ON CONFLICT" phrase.
</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 <a href="lang_createtable.html#uniqueconst">UNIQUE</a>, <a href="lang_createtable.html#notnullconst">NOT NULL</a>,
<a href="lang_createtable.html#ckconst">CHECK</a>, and <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> constraints.
The ON CONFLICT algorithm does not
apply to <a href="foreignkeys.html">FOREIGN KEY constraints</a>.
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>
<dd><p> When an applicable constraint violation occurs, the ROLLBACK
resolution algorithm aborts the current SQL statement with
an SQLITE_CONSTRAINT error and rolls back the current transaction.
If no transaction is
active (other than the implied transaction that is created on every
command) then the ROLLBACK resolution algorithm works the same as the
ABORT algorithm.</p></dd>
<dt><b>ABORT</b></dt>
<dd><p> When an applicable constraint violation occurs, the ABORT
resolution algorithm aborts the current SQL statement
with an SQLITE_CONSTRAINT error and backs out any changes
made by the current SQL statement; but changes caused
by prior SQL statements within the same transaction are preserved and the
transaction remains active.
This is the default behavior and the behavior specified by the SQL
standard.</p></dd>
<dt><b>FAIL</b></dt>
<dd><p> When an applicable constraint violation occurs, the FAIL
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 <a href="foreignkeys.html">foreign key constraint</a> 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 <a href="foreignkeys.html">foreign key constraint</a> errors.
</p>
</dd>
<dt><b>REPLACE</b></dt>
<dd><p> When a <a href="lang_createtable.html#uniqueconst">UNIQUE</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> 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 <a href="lang_createtable.html#notnullconst">NOT NULL</a> 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 <a href="lang_createtable.html#ckconst">CHECK constraint</a> or <a href="foreignkeys.html">foreign key constraint</a> 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, <a href="lang_createtrigger.html">delete triggers</a> fire if and only if
<a href="pragma.html#pragma_recursive_triggers">recursive triggers</a> are enabled.</p>
<p>The <a href="c3ref/update_hook.html">update hook</a> is not invoked for rows that
are deleted by the REPLACE conflict resolution strategy. Nor does
REPLACE increment the <a href="c3ref/changes.html">change counter</a>.
The exceptional behaviors defined in this paragraph might change
in a future release.</p>
</dd></dl>
<p>The algorithm specified in the OR clause of an INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/lang_conflict.in?m=99e70e264342d2774">2021-11-01 21:09:36</a> UTC </small></i></p>
|
