📄 lang_conflict.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>SQLite Query Language: ON CONFLICT clause</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <a href="lang.html"><h2>SQL As Understood By SQLite</h2></a><h3>ON CONFLICT clause</h3><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">conflict-clause</font></i> ::=</td><td><b><font color="#2c2cf0">ON CONFLICT </font></b><i><font color="#ff3434">conflict-algorithm</font></i><b><font color="#2c2cf0"></font></b></td></tr><tr><td align="right" valign="top"><i><font color="#ff3434">conflict-algorithm</font></i> ::=</td><td><b><font color="#2c2cf0">ROLLBACK </font></b><big>|</big><b><font color="#2c2cf0"> ABORT </font></b><big>|</big><b><font color="#2c2cf0"> FAIL </font></b><big>|</big><b><font color="#2c2cf0"> IGNORE </font></b><big>|</big><b><font color="#2c2cf0"> REPLACE</font></b></td></tr></table><p>The ON CONFLICT clause is not a separate SQL command. It is anon-standard clause that can appear in many other SQL commands.It is given its own section in this document because it is notpart of standard SQL and therefore might not be familiar.</p><p>The syntax for the ON CONFLICT clause is as shown above forthe CREATE TABLE command. For the INSERT andUPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to makethe 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 sameeither way.</p><p>The ON CONFLICT clause specifies an algorithm used to resolveconstraint conflicts. There are five choices: ROLLBACK, ABORT,FAIL, IGNORE, and REPLACE. The default algorithm is ABORT. Thisis what they mean:</p><dl><dt><b>ROLLBACK</b></dt><dd><p>When a constraint violation occurs, an immediate ROLLBACKoccurs, thus ending the current transaction, and the command abortswith a return code of SQLITE_CONSTRAINT. If no transaction isactive (other than the implied transaction that is created on everycommand) then this algorithm works the same as ABORT.</p></dd><dt><b>ABORT</b></dt><dd><p>When a constraint violation occurs, the command backs outany prior changes it might have made and aborts with a return codeof SQLITE_CONSTRAINT. But no ROLLBACK is executed so changesfrom prior commands within the same transactionare preserved. This is the default behavior.</p></dd><dt><b>FAIL</b></dt><dd><p>When a constraint violation occurs, the command aborts with areturn code SQLITE_CONSTRAINT. But any changes to the database thatthe command made prior to encountering the constraint violationare preserved and are not backed out. For example, if an UPDATEstatement encountered a constraint violation on the 100th row thatit attempts to update, then the first 99 row changes are preservedbut changes to rows 100 and beyond never occur.</p></dd><dt><b>IGNORE</b></dt><dd><p>When a constraint violation occurs, the one row that containsthe constraint violation is not inserted or changed. But the commandcontinues executing normally. Other rows before and after the row thatcontained the constraint violation continue to be inserted or updatednormally. No error is returned.</p></dd><dt><b>REPLACE</b></dt><dd><p>When a UNIQUE constraint violation occurs, the pre-existing rowsthat are causing the constraint violation are removed prior to insertingor updating the current row. Thus the insert or update always occurs.The command continues executing normally. No error is returned.If a NOT NULL constraint violation occurs, the NULL value is replacedby the default value for that column. If the column has no defaultvalue, then the ABORT algorithm is used. If a CHECK constraint violationoccurs then the IGNORE algorithm is used.</p><p>When this conflict resolution strategy deletes rows in order tosatisfy a constraint, it does not invoke delete triggers on thoserows. This behavior might change in a future release.</p></dl><p>The algorithm specified in the OR clause of a INSERT or UPDATEoverrides any algorithm specified in a CREATE TABLE.If no algorithm is specified anywhere, the ABORT algorithm is used.</p><hr><small><i>This page last modified 2008/04/27 17:48:52 UTC</i></small></div></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -