Debian Codegraph @

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