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
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<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>Rowid Tables</title>
</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_div("searchmenu")'>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">
<span class="desktoponly">Search for:</span> <input type="text" name="q">
<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 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.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;
}
</script>
</div>
<h1 align="center">Rowid Tables</h1>
<h2>1.0 Definition</h2>
<p>A "rowid table" is any table in an SQLite schema that
<ul>
<li>is <em>not</em> a <a href="vtab.html">virtual table</a>, and
<li>is <em>not</em> a <a href="withoutrowid.html">WITHOUT ROWID</a> table.
</ul>
Most tables in a typical SQLite database schema are rowid tables.
<p>Rowid tables are distinguished by the fact that they all have
a unique, non-NULL, signed 64-bit integer <a href="lang_createtable.html#rowid">rowid</a> that is used as
the access key for the data in the underlying <a href="fileformat2.html#btree">B-tree</a> storage engine.
<h2>2.0 Quirks</h2>
<ul>
<li><p>
The <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> of a rowid table (if there is one) is usually not the
true primary key for the table, in the sense that it is not the unique
key used by the underlying <a href="fileformat2.html#btree">B-tree</a> storage engine. The exception to
this rule is when the rowid table declares an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>.
In the exception, the INTEGER PRIMARY KEY becomes an alias for the
<a href="lang_createtable.html#rowid">rowid</a>.
<li><p>
The true primary key for a rowid table (the value that is used as the
key to look up rows in the underlying <a href="fileformat2.html#btree">B-tree</a> storage engine)
is the <a href="lang_createtable.html#rowid">rowid</a>.
<li><p>
The PRIMARY KEY constraint for a rowid table (as long as it is not
the true primary key or INTEGER PRIMARY KEY) is really the same thing
as a <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a>. Because it is not a true primary key,
columns of the PRIMARY KEY are allowed to be NULL, in violation of
all SQL standards.
<li><p>
The <a href="lang_createtable.html#rowid">rowid</a> of a rowid table can be accessed (or changed) by reading or
writing to any of the "rowid" or "oid" or "_rowid_" columns. Except,
if there is a declared columns in the table that use those
special names, then those names refer to the declared columns, not to
the underlying <a href="lang_createtable.html#rowid">rowid</a>.
<li><p>
Access to records via <a href="lang_createtable.html#rowid">rowid</a> is highly optimized and very fast.
<li><p>
If the <a href="lang_createtable.html#rowid">rowid</a> is not aliased by <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> then it is not
persistent and might change. In particular the <a href="lang_vacuum.html">VACUUM</a> command will
change rowids for tables that do not declare an INTEGER PRIMARY KEY.
Therefore, applications should not normally access the rowid directly,
but instead use an INTEGER PRIMARY KEY.
<li><p>
In the underlying <a href="fileformat2.html">file format</a>, each rowid is stored as a
<a href="fileformat2.html#varint">variable-length integer</a>. That means that small non-negative
rowid values take up less disk space than large or negative
rowid values.
<li><p>
All of the complications above (and others not mentioned here)
arise from the need to preserve backwards
compatibility to the tens of billions of SQLite database files in
circulation. In a perfect world, there would be no such thing as a "rowid"
and all tables would following the standard semantics implemented as
<a href="withoutrowid.html">WITHOUT ROWID</a> tables, only without the extra "WITHOUT ROWID" keywords.
Unfortunately, life is messy. The designer of SQLite offers his
sincere apology for the current mess.
</ul>
|
