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>Constraint Conflict Resolution in SQLite</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>
122
<h1>Constraint Conflict Resolution in SQLite</h1>
125
In most SQL databases, if you have a UNIQUE constraint on
126
a table and you try to do an UPDATE or INSERT that violates
127
the constraint, the database will abort the operation in
128
progress, back out any prior changes associated with the same
129
UPDATE or INSERT statement, and return an error.
130
This is the default behavior of SQLite, though SQLite also allows one to
131
define alternative ways for dealing with constraint violations.
132
This article describes those alternatives and how to use them.
135
<h2>Conflict Resolution Algorithms</h2>
138
SQLite defines five constraint conflict resolution algorithms
143
<dt><b>ROLLBACK</b></dt>
144
<dd><p>When a constraint violation occurs, an immediate ROLLBACK
145
occurs, thus ending the current transaction, and the command aborts
146
with a return code of SQLITE_CONSTRAINT. If no transaction is
147
active (other than the implied transaction that is created on every
148
command) then this algorithm works the same as ABORT.</p></dd>
150
<dt><b>ABORT</b></dt>
151
<dd><p>When a constraint violation occurs, the command backs out
152
any prior changes it might have made and aborts with a return code
153
of SQLITE_CONSTRAINT. But no ROLLBACK is executed so changes
154
from prior commands within the same transaction
155
are preserved. This is the default behavior for SQLite.</p></dd>
158
<dd><p>When a constraint violation occurs, the command aborts with a
159
return code SQLITE_CONSTRAINT. But any changes to the database that
160
the command made prior to encountering the constraint violation
161
are preserved and are not backed out. For example, if an UPDATE
162
statement encountered a constraint violation on the 100th row that
163
it attempts to update, then the first 99 row changes are preserved
164
but change to rows 100 and beyond never occur.</p></dd>
166
<dt><b>IGNORE</b></dt>
167
<dd><p>When a constraint violation occurs, the one row that contains
168
the constraint violation is not inserted or changed. But the command
169
continues executing normally. Other rows before and after the row that
170
contained the constraint violation continue to be inserted or updated
171
normally. No error is returned.</p></dd>
173
<dt><b>REPLACE</b></dt>
174
<dd><p>When a UNIQUE constraint violation occurs, the pre-existing row
175
that caused the constraint violation is removed prior to inserting
176
or updating the current row. Thus the insert or update always occurs.
177
The command continues executing normally. No error is returned.</p></dd>