Debian Codegraph @

www/lang_conflict.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>The ON CONFLICT Clause</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The ON CONFLICT Clause
122 </div>
123 </div>
124
125
126
127
128 <p><b><a href="syntax/conflict-clause.html">conflict-clause:</a></b>
129 <button id='x04db8a56' onclick='hideorshow("x04db8a56","xbed20bda")'>hide</button></p>
130 <div id='xbed20bda' class='imgcontainer'>
131 <div style="max-width:451px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 451.517 205.2" data-pikchr-date="20250319161943">
132 <circle cx="5.76" cy="6.48" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
133 <polygon points="32.4,6.48 20.88,10.8 20.88,2.16" style="fill:rgb(0,0,0)"/>
134 <path d="M9.36,6.48L26.64,6.48" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
135 <polygon points="70.44,36.72 58.92,41.04 58.92,32.4" style="fill:rgb(0,0,0)"/>
136 <path d="M32.4,6.48 L 39.9,6.48 Q 47.4,6.48 47.4,21.48 L 47.4,21.72 Q 47.4,36.72 56.04,36.72 L 64.68,36.72" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
137 <path d="M85.56,51.84L94.488,51.84A15.12 15.12 0 0 0 109.608 36.72L109.608,36.72A15.12 15.12 0 0 0 94.488 21.6L85.56,21.6A15.12 15.12 0 0 0 70.44 36.72L70.44,36.72A15.12 15.12 0 0 0 85.56 51.84Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
138 <text x="90.024" y="36.72" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ON</text>
139 <polygon points="132.648,36.72 121.128,41.04 121.128,32.4" style="fill:rgb(0,0,0)"/>
140 <path d="M109.608,36.72L126.888,36.72" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
141 <path d="M147.768,51.84L222.13,51.84A15.12 15.12 0 0 0 237.25 36.72L237.25,36.72A15.12 15.12 0 0 0 222.13 21.6L147.768,21.6A15.12 15.12 0 0 0 132.648 36.72L132.648,36.72A15.12 15.12 0 0 0 147.768 51.84Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
142 <text x="184.949" y="36.72" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">CONFLICT</text>
143 <polygon points="273.25,36.72 261.73,41.04 261.73,32.4" style="fill:rgb(0,0,0)"/>
144 <path d="M237.25,36.72L267.49,36.72" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
145 <path d="M288.37,51.84L365.957,51.84A15.12 15.12 0 0 0 381.077 36.72L381.077,36.72A15.12 15.12 0 0 0 365.957 21.6L288.37,21.6A15.12 15.12 0 0 0 273.25 36.72L273.25,36.72A15.12 15.12 0 0 0 288.37 51.84Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
146 <text x="327.163" y="36.72" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ROLLBACK</text>
147 <path d="M288.37,89.64L330.245,89.64A15.12 15.12 0 0 0 345.365 74.52L345.365,74.52A15.12 15.12 0 0 0 330.245 59.4L288.37,59.4A15.12 15.12 0 0 0 273.25 74.52L273.25,74.52A15.12 15.12 0 0 0 288.37 89.64Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
148 <text x="309.307" y="74.52" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ABORT</text>
149 <path d="M288.37,127.44L310.315,127.44A15.12 15.12 0 0 0 325.435 112.32A15.12 15.12 0 0 0 310.315 97.2L288.37,97.2A15.12 15.12 0 0 0 273.25 112.32A15.12 15.12 0 0 0 288.37 127.44Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
150 <text x="299.342" y="112.32" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">FAIL</text>
151 <path d="M288.37,165.24L340.498,165.24A15.12 15.12 0 0 0 355.618 150.12A15.12 15.12 0 0 0 340.498 135L288.37,135A15.12 15.12 0 0 0 273.25 150.12A15.12 15.12 0 0 0 288.37 165.24Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
152 <text x="314.434" y="150.12" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">IGNORE</text>
153 <path d="M288.37,203.04L352.478,203.04A15.12 15.12 0 0 0 367.598 187.92A15.12 15.12 0 0 0 352.478 172.8L288.37,172.8A15.12 15.12 0 0 0 273.25 187.92A15.12 15.12 0 0 0 288.37 203.04Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
154 <text x="320.424" y="187.92" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">REPLACE</text>
155 <polygon points="404.117,36.72 392.597,41.04 392.597,32.4" style="fill:rgb(0,0,0)"/>
156 <path d="M381.077,36.72L398.357,36.72" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
157 <polygon points="442.157,6.48 430.637,10.8 430.637,2.16" style="fill:rgb(0,0,0)"/>
158 <path d="M404.117,36.72 L 411.617,36.72 Q 419.117,36.72 419.117,21.72 L 419.117,21.48 Q 419.117,6.48 427.757,6.48 L 436.397,6.48" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
159 <circle cx="445.757" cy="6.48" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
160 <polygon points="225.758,6.48 214.238,10.8 214.238,2.16" style="fill:rgb(0,0,0)"/>
161 <path d="M9.36,6.48L219.998,6.48" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
162 <path d="M225.758,6.48L434.237,6.48" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
163 <polygon points="273.25,187.92 261.73,192.24 261.73,183.6" style="fill:rgb(0,0,0)"/>
164 <path d="M237.25,36.72 L 244.75,36.72 Q 252.25,36.72 252.25,51.72 L 252.25,172.92 Q 252.25,187.92 259.87,187.92 L 267.49,187.92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
165 <polygon points="404.117,187.92 392.597,192.24 392.597,183.6" style="fill:rgb(0,0,0)"/>
166 <path d="M367.598,187.92L398.357,187.92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
167 <path d="M404.117,187.92 L 411.617,187.92 Q 419.117,187.92 419.117,172.92 L 419.117,36.6 L 419.117,21.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
168 <polygon points="273.25,74.52 261.73,78.84 261.73,70.2" style="fill:rgb(0,0,0)"/>
169 <path d="M252.25,59.4 L 252.25,66.96 Q 252.25,74.52 259.87,74.52 L 267.49,74.52" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
170 <polygon points="404.117,74.52 392.597,78.84 392.597,70.2" style="fill:rgb(0,0,0)"/>
171 <path d="M345.365,74.52L398.357,74.52" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
172 <path d="M404.117,74.52 L 411.617,74.52 Q 419.117,74.52 419.117,67.02 L 419.117,59.52" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
173 <polygon points="273.25,112.32 261.73,116.64 261.73,108" style="fill:rgb(0,0,0)"/>
174 <path d="M252.25,97.2 L 252.25,104.76 Q 252.25,112.32 259.87,112.32 L 267.49,112.32" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
175 <polygon points="404.117,112.32 392.597,116.64 392.597,108" style="fill:rgb(0,0,0)"/>
176 <path d="M325.435,112.32L398.357,112.32" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
177 <path d="M404.117,112.32 L 411.617,112.32 Q 419.117,112.32 419.117,104.82 L 419.117,97.32" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
178 <polygon points="273.25,150.12 261.73,154.44 261.73,145.8" style="fill:rgb(0,0,0)"/>
179 <path d="M252.25,135 L 252.25,142.56 Q 252.25,150.12 259.87,150.12 L 267.49,150.12" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
180 <polygon points="404.117,150.12 392.597,154.44 392.597,145.8" style="fill:rgb(0,0,0)"/>
181 <path d="M355.618,150.12L398.357,150.12" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
182 <path d="M404.117,150.12 L 411.617,150.12 Q 419.117,150.12 419.117,142.62 L 419.117,135.12" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
183 </svg>
184 </div>
185 </div>
186
187
188 <p>The ON CONFLICT clause is a
189 non-standard extension specific to SQLite
190 that can appear in many other SQL commands.
191 It is given its own section in this document because it is not
192 part of standard SQL and therefore might not be familiar.</p>
193
194 <p>The ON CONFLICT clause described here has been a part of
195 SQLite since before version 3.0.0 (2004-06-18). The phrase
196 "ON&nbsp;CONFLICT" is also part of <a href="lang_upsert.html">UPSERT</a>, which is an extension
197 to <a href="lang_insert.html">INSERT</a> added in version 3.24.0 (2018-06-04). Do not
198 confuse these two separate uses of the "ON&nbsp;CONFLICT" phrase.
199
200 </p><p>The syntax for the ON CONFLICT clause is as shown above for
201 the CREATE TABLE command. For the INSERT and
202 UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that
203 the syntax reads more naturally. For example, instead of
204 "INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
205 The keywords change but the meaning of the clause is the same
206 either way.</p>
207
208 <p>The ON CONFLICT clause is allowed on <a href="lang_createtable.html#uniqueconst">UNIQUE</a>, <a href="lang_createtable.html#notnullconst">NOT NULL</a>,
209 and <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> constraints only.
210 The ON CONFLICT clause is not allowed on
211 <a href="lang_createtable.html#ckconst">CHECK</a> and <a href="foreignkeys.html">FOREIGN KEY constraints</a>.
212 There are five conflict resolution algorithm choices:
213 ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE.
214 The default conflict resolution algorithm is ABORT. This
215 is what they mean:</p>
216
217 <dl>
218 <dt><b>ROLLBACK</b></dt>
219 <dd><p> When an applicable constraint violation occurs, the ROLLBACK
220 resolution algorithm aborts the current SQL statement with
221 an SQLITE_CONSTRAINT error and rolls back the current transaction.
222 If no transaction is
223 active (other than the implied transaction that is created on every
224 command) then the ROLLBACK resolution algorithm works the same as the
225 ABORT algorithm.</p></dd>
226
227 <dt><b>ABORT</b></dt>
228 <dd><p> When an applicable constraint violation occurs, the ABORT
229 resolution algorithm aborts the current SQL statement
230 with an SQLITE_CONSTRAINT error and backs out any changes
231 made by the current SQL statement; but changes caused
232 by prior SQL statements within the same transaction are preserved and the
233 transaction remains active.
234 This is the default behavior and the behavior specified by the SQL
235 standard.</p></dd>
236
237 <dt><b>FAIL</b></dt>
238 <dd><p> When an applicable constraint violation occurs, the FAIL
239 resolution algorithm aborts the current SQL statement with an
240 SQLITE_CONSTRAINT error. But the FAIL resolution does not
241 back out prior changes of the SQL statement that failed nor does
242 it end the transaction.
243 For example, if an UPDATE
244 statement encountered a constraint violation on the 100th row that
245 it attempts to update, then the first 99 row changes are preserved
246 but changes to rows 100 and beyond never occur.</p>
247
248 <p>The FAIL behavior only works for uniqueness, NOT NULL, and CHECK
249 constraints. A <a href="foreignkeys.html">foreign key constraint</a> violation causes an ABORT.
250 </p></dd>
251
252 <dt><b>IGNORE</b></dt>
253 <dd><p> When an applicable constraint violation occurs,
254 the IGNORE resolution algorithm skips the one row that contains
255 the constraint violation and continues processing subsequent rows
256 of the SQL statement as if nothing went wrong.
257 Other rows before and after the row that
258 contained the constraint violation are inserted or updated
259 normally. No error is returned for uniqueness, NOT NULL, and
260 UNIQUE constraint errors when the IGNORE conflict resolution
261 algorithm is used. However, the IGNORE conflict resolution
262 algorithm works like ABORT for <a href="foreignkeys.html">foreign key constraint</a> errors.
263 </p>
264 </dd>
265
266 <dt><b>REPLACE</b></dt>
267 <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,
268 the REPLACE algorithm
269 deletes pre-existing rows that are causing the constraint violation
270 prior to inserting or updating the current row and the command continues
271 executing normally.
272 If a <a href="lang_createtable.html#notnullconst">NOT NULL</a> constraint violation occurs, the REPLACE conflict
273 resolution replaces the NULL value with
274 the default value for that column, or if the column has no default
275 value, then the ABORT algorithm is used.
276 If a <a href="lang_createtable.html#ckconst">CHECK constraint</a> or <a href="foreignkeys.html">foreign key constraint</a> violation occurs,
277 the REPLACE conflict resolution algorithm works like ABORT.</p>
278
279 <p>When the REPLACE conflict resolution strategy deletes rows in order to
280 satisfy a constraint, <a href="lang_createtrigger.html">delete triggers</a> fire if and only if
281 <a href="pragma.html#pragma_recursive_triggers">recursive triggers</a> are enabled.</p>
282
283 <p>The <a href="c3ref/update_hook.html">update hook</a> is not invoked for rows that
284 are deleted by the REPLACE conflict resolution strategy. Nor does
285 REPLACE increment the <a href="c3ref/changes.html">change counter</a>.
286 The exceptional behaviors defined in this paragraph might change
287 in a future release.</p>
288 </dd></dl>
289
290 <p>The algorithm specified in the OR clause of an INSERT or UPDATE
291 overrides any algorithm specified in a CREATE TABLE.
292 If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
293 <p align="center"><small><i>This page was last updated on 2025-11-22 13:15:11Z </small></i></p>
294