Debian Codegraph @

www/autoinc.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>SQLite Autoincrement</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 SQLite Autoincrement
122 </div>
123 </div>
124
125
126
127
128 <h1 id="summary"><span>1. </span>Summary</h1>
129
130 <ol type="1">
131 <li><p>
132 The AUTOINCREMENT keyword imposes extra CPU, memory, disk space,
133 and disk I/O overhead and should be avoided if not strictly needed.
134 It is usually not needed.
135 </p></li><li><p>
136 In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the <a href="lang_createtable.html#rowid">ROWID</a>
137 (except in <a href="withoutrowid.html">WITHOUT ROWID</a> tables) which is always a 64-bit signed integer.
138 </p></li><li><p>
139 On an <a href="lang_insert.html">INSERT</a>, if the ROWID or INTEGER PRIMARY KEY column is not
140 explicitly given a value, then it
141 will be filled automatically with an unused integer, usually
142 one more than the largest ROWID currently in use.
143 This is true regardless of whether or not the AUTOINCREMENT keyword is used.
144 </p></li><li><p>
145 If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that
146 changes the automatic ROWID assignment algorithm to prevent
147 the reuse of ROWIDs over the lifetime of the database. In other words,
148 the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from
149 previously deleted rows.
150 </p></li></ol>
151
152 <h1 id="background"><span>2. </span>Background</h1>
153
154 <p>
155 In SQLite, table rows normally have a 64-bit signed integer <a href="lang_createtable.html#rowid">ROWID</a>
156 which is unique among all rows in the same table.
157 (<a href="withoutrowid.html">WITHOUT ROWID</a> tables are the exception.)
158 </p>
159
160 <p>
161 You can access the ROWID of an SQLite table using one of the special column
162 names ROWID, _ROWID_, or OID.
163 Except if you declare an ordinary table column to use one of those special
164 names, then the use of that name will refer to the declared column not
165 to the internal ROWID.
166 </p>
167
168 <p>
169 If a table contains a column of type <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, then that
170 column becomes an alias for the ROWID. You can then access the ROWID
171 using any of four different names, the original three names described above
172 or the name given to the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column. All these names are
173 aliases for one another and work equally well in any context.
174 </p>
175
176 <p>
177 When a new row is inserted into an SQLite table, the ROWID can either
178 be specified as part of the INSERT statement or it can be assigned
179 automatically by the database engine. To specify a ROWID manually,
180 just include it in the list of values to be inserted. For example:
181 </p>
182
183 <div class="codeblock"><pre>CREATE TABLE test1(a INT, b TEXT);
184 INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
185 </pre></div>
186
187 <p>
188 If no ROWID is specified on the insert, or if the specified ROWID has a value
189 of NULL, then an appropriate ROWID is created
190 automatically. The usual algorithm is to give the newly created row
191 a ROWID that is one larger than the largest ROWID in the table prior
192 to the insert. If the table is initially empty, then a ROWID of 1 is
193 used. If the largest ROWID is equal to the largest possible integer
194 (9223372036854775807) then the database
195 engine starts picking positive candidate ROWIDs at random until it finds one
196 that is not previously used.
197 If no unused ROWID can be found after a reasonable number of attempts,
198 the insert operation fails with an <a href="rescode.html#full">SQLITE_FULL</a> error.
199 If no negative ROWID values are inserted explicitly, then automatically
200 generated ROWID values will always be greater than zero.
201 </p>
202
203 <p>
204 The normal ROWID selection algorithm described above
205 will generate monotonically increasing
206 unique ROWIDs as long as you never use the maximum ROWID value and you never
207 delete the entry in the table with the largest ROWID.
208 If you ever delete rows or if you ever create a row with the maximum possible
209 ROWID, then ROWIDs from previously deleted rows might be reused when creating
210 new rows and newly created ROWIDs might not be in strictly ascending order.
211 </p>
212
213
214 <h1 id="the_autoincrement_keyword"><span>3. </span>The AUTOINCREMENT Keyword</h1>
215
216 <p>
217 If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
218 different ROWID selection algorithm is used.
219 The ROWID chosen for the new row is at least one larger than the largest ROWID
220 that has ever before existed in that same table. If the table has never
221 before contained any data, then a ROWID of 1 is used. If the largest possible
222 ROWID has previously been inserted, then
223 new INSERTs are not allowed and any attempt to insert a new row will
224 fail with an SQLITE_FULL error.
225 Only ROWID values from previous transactions that
226 were committed are considered. ROWID values that were rolled back
227 are ignored and can be reused.
228 </p>
229
230 <p>
231 SQLite keeps track of the largest ROWID
232 using an <a href="fileformat2.html#intschema">internal table</a> named "<a href="fileformat2.html#seqtab">sqlite_sequence</a>".
233 The sqlite_sequence table is created automatically, if it does not
234 already exist, whenever a normal table that contains an
235 AUTOINCREMENT column is created. A row in the sqlite_sequence table
236 corresponding to the table with the AUTOINCREMENT column is created the
237 first time the AUTOINCREMENT table is written and updated on any
238 subsequent writes that increase the maximum rowid.
239 The content of the sqlite_sequence table
240 can be modified using ordinary UPDATE, INSERT, and DELETE statements.
241 But making modifications to this table will likely perturb the AUTOINCREMENT
242 key generation algorithm. Make sure you know what you are doing before
243 you undertake such changes.
244 The sqlite_sequence table does not track ROWID changes associated with
245 UPDATE statement, only INSERT statements.
246 </p>
247
248 <p>
249 The behavior implemented by the AUTOINCREMENT keyword is subtly different
250 from the default behavior. With AUTOINCREMENT, rows with automatically
251 selected ROWIDs are guaranteed to have ROWIDs that have never been used
252 before by the same table in the same database. And the automatically generated
253 ROWIDs are guaranteed to be monotonically increasing. These are important
254 properties in certain applications. But if your application does not
255 need these properties, you should probably stay with the default behavior
256 since the use of AUTOINCREMENT requires additional work to be done
257 as each row is inserted and thus causes INSERTs to run a little slower.
258 </p>
259
260 <p>Note that "monotonically increasing" does not imply that the ROWID always
261 increases by exactly one. One is the usual increment. However, if an
262 insert fails due to (for example) a uniqueness constraint, the ROWID of
263 the failed insertion attempt might not be reused on subsequent inserts,
264 resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that
265 automatically chosen ROWIDs will be increasing but not that they will be
266 sequential.</p>
267
268 <p>Because AUTOINCREMENT keyword changes the behavior of the ROWID selection
269 algorithm, AUTOINCREMENT is not allowed on <a href="withoutrowid.html">WITHOUT ROWID</a> tables or on any
270 table column other than INTEGER PRIMARY KEY. Any attempt to use
271 AUTOINCREMENT on a <a href="withoutrowid.html">WITHOUT ROWID</a> table or on a column other than the
272 INTEGER PRIMARY KEY column results in an error.</p>
273 <p align="center"><small><i>This page was last updated on 2024-02-22 16:13:38Z </small></i></p>
274