1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite Autoincrement</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
SQLite Autoincrement
</div>
</div>
<h1 id="summary"><span>1. </span>Summary</h1>
<ol type="1">
<li><p>
The AUTOINCREMENT keyword imposes extra CPU, memory, disk space,
and disk I/O overhead and should be avoided if not strictly needed.
It is usually not needed.
</p></li><li><p>
In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the <a href="lang_createtable.html#rowid">ROWID</a>
(except in <a href="withoutrowid.html">WITHOUT ROWID</a> tables) which is always a 64-bit signed integer.
</p></li><li><p>
On an <a href="lang_insert.html">INSERT</a>, if the ROWID or INTEGER PRIMARY KEY column is not
explicitly given a value, then it
will be filled automatically with an unused integer, usually
one more than the largest ROWID currently in use.
This is true regardless of whether or not the AUTOINCREMENT keyword is used.
</p></li><li><p>
If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that
changes the automatic ROWID assignment algorithm to prevent
the reuse of ROWIDs over the lifetime of the database. In other words,
the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from
previously deleted rows.
</p></li></ol>
<h1 id="background"><span>2. </span>Background</h1>
<p>
In SQLite, table rows normally have a 64-bit signed integer <a href="lang_createtable.html#rowid">ROWID</a>
which is unique among all rows in the same table.
(<a href="withoutrowid.html">WITHOUT ROWID</a> tables are the exception.)
</p>
<p>
You can access the ROWID of an SQLite table using one of the special column
names ROWID, _ROWID_, or OID.
Except if you declare an ordinary table column to use one of those special
names, then the use of that name will refer to the declared column not
to the internal ROWID.
</p>
<p>
If a table contains a column of type <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, then that
column becomes an alias for the ROWID. You can then access the ROWID
using any of four different names, the original three names described above
or the name given to the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column. All these names are
aliases for one another and work equally well in any context.
</p>
<p>
When a new row is inserted into an SQLite table, the ROWID can either
be specified as part of the INSERT statement or it can be assigned
automatically by the database engine. To specify a ROWID manually,
just include it in the list of values to be inserted. For example:
</p>
<div class="codeblock"><pre>CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
</pre></div>
<p>
If no ROWID is specified on the insert, or if the specified ROWID has a value
of NULL, then an appropriate ROWID is created
automatically. The usual algorithm is to give the newly created row
a ROWID that is one larger than the largest ROWID in the table prior
to the insert. If the table is initially empty, then a ROWID of 1 is
used. If the largest ROWID is equal to the largest possible integer
(9223372036854775807) then the database
engine starts picking positive candidate ROWIDs at random until it finds one
that is not previously used.
If no unused ROWID can be found after a reasonable number of attempts,
the insert operation fails with an <a href="rescode.html#full">SQLITE_FULL</a> error.
If no negative ROWID values are inserted explicitly, then automatically
generated ROWID values will always be greater than zero.
</p>
<p>
The normal ROWID selection algorithm described above
will generate monotonically increasing
unique ROWIDs as long as you never use the maximum ROWID value and you never
delete the entry in the table with the largest ROWID.
If you ever delete rows or if you ever create a row with the maximum possible
ROWID, then ROWIDs from previously deleted rows might be reused when creating
new rows and newly created ROWIDs might not be in strictly ascending order.
</p>
<h1 id="the_autoincrement_keyword"><span>3. </span>The AUTOINCREMENT Keyword</h1>
<p>
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
different ROWID selection algorithm is used.
The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before existed in that same table. If the table has never
before contained any data, then a ROWID of 1 is used. If the largest possible
ROWID has previously been inserted, then
new INSERTs are not allowed and any attempt to insert a new row will
fail with an SQLITE_FULL error.
Only ROWID values from previous transactions that
were committed are considered. ROWID values that were rolled back
are ignored and can be reused.
</p>
<p>
SQLite keeps track of the largest ROWID
using an <a href="fileformat2.html#intschema">internal table</a> named "<a href="fileformat2.html#seqtab">sqlite_sequence</a>".
The sqlite_sequence table is created
and initialized automatically whenever a normal table that contains an
AUTOINCREMENT column is created. The content of the sqlite_sequence table
can be modified using ordinary UPDATE, INSERT, and DELETE statements.
But making modifications to this table will likely perturb the AUTOINCREMENT
key generation algorithm. Make sure you know what you are doing before
you undertake such changes.
The sqlite_sequence table does not track ROWID changes associated with
UPDATE statement, only INSERT statements.
</p>
<p>
The behavior implemented by the AUTOINCREMENT keyword is subtly different
from the default behavior. With AUTOINCREMENT, rows with automatically
selected ROWIDs are guaranteed to have ROWIDs that have never been used
before by the same table in the same database. And the automatically generated
ROWIDs are guaranteed to be monotonically increasing. These are important
properties in certain applications. But if your application does not
need these properties, you should probably stay with the default behavior
since the use of AUTOINCREMENT requires additional work to be done
as each row is inserted and thus causes INSERTs to run a little slower.
</p>
<p>Note that "monotonically increasing" does not imply that the ROWID always
increases by exactly one. One is the usual increment. However, if an
insert fails due to (for example) a uniqueness constraint, the ROWID of
the failed insertion attempt might not be reused on subsequent inserts,
resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that
automatically chosen ROWIDs will be increasing but not that they will be
sequential.</p>
<p>Because AUTOINCREMENT keyword changes the behavior of the ROWID selection
algorithm, AUTOINCREMENT is not allowed on <a href="withoutrowid.html">WITHOUT ROWID</a> tables or on any
table column other than INTEGER PRIMARY KEY. Any attempt to use
AUTOINCREMENT on a <a href="withoutrowid.html">WITHOUT ROWID</a> table or on a column other than the
INTEGER PRIMARY KEY column results in an error.</p>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/autoinc.in?m=bd2decf99d">2022-01-08 05:02:57</a> UTC </small></i></p>
|
