Documentation Source Text

Check-in [60668454be]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Clarification and amplification of AUTOINCREMENT.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 60668454beae79bbcf8990d938a0252a7dd24be5
User & Date: drh 2014-08-15 13:36:50.778
Context
2014-08-15
13:45
Add the sha1sum and source-id for version 3.8.6 to the changelog. (check-in: 1e9ff29172 user: dan tags: trunk)
13:36
Clarification and amplification of AUTOINCREMENT. (check-in: 60668454be user: drh tags: trunk)
00:49
Clarifications to the ON CONFLICT documentation. (check-in: 445adeb211 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/autoinc.in. 
1
2
3



























4
5
6
7
8
9
10

<title>SQLite Autoincrement</title>
<h1>SQLite Autoincrement</h1>
<tcl>hd_keywords AUTOINCREMENT</tcl>




























<p>
^In SQLite, table rows normally have a 64-bit signed integer [ROWID]
which is unique among all rows in the same table.
([WITHOUT ROWID] tables are the exception.)
</p>



<

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








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

<title>SQLite Autoincrement</title>

<tcl>hd_keywords AUTOINCREMENT</tcl>
<h1 align=center>Autoincrement In SQLite</h1>

<h2>Summary</h2>

<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.
<li><p>
  In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the [ROWID]
  (except in [WITHOUT ROWID] tables) which is always a 64-bit signed integer.
<li><p>
  On an [INSERT], 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 the
  one more than the largest ROWID currently in use.
  This is true regardless of whether or not the AUTOINCREMENT keyword is used.
<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.
</ol>

<h2>Background</h2>

<p>
^In SQLite, table rows normally have a 64-bit signed integer [ROWID]
which is unique among all rows in the same table.
([WITHOUT ROWID] tables are the exception.)
</p>
︙︙