Debian Codegraph @

www/rowidtable.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>Rowid Tables</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 align="center">Rowid Tables</h1>
121
122 <h2>1.0 Definition</h2>
123
124 <p>A "rowid table" is any table in an SQLite schema that
125 <ul>
126 <li>is <em>not</em> a <a href="vtab.html">virtual table</a>, and
127 <li>is <em>not</em> a <a href="withoutrowid.html">WITHOUT ROWID</a> table.
128 </ul>
129 Most tables in a typical SQLite database schema are rowid tables.
130
131 <p>Rowid tables are distinguished by the fact that they all have
132 a unique, non-NULL, signed 64-bit integer <a href="lang_createtable.html#rowid">rowid</a> that is used as
133 the access key for the data in the underlying <a href="fileformat2.html#btree">B-tree</a> storage engine.
134
135 <h2>2.0 Quirks</h2>
136
137 <ul>
138 <li><p>
139 The <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> of a rowid table (if there is one) is usually not the
140 true primary key for the table, in the sense that it is not the unique
141 key used by the underlying <a href="fileformat2.html#btree">B-tree</a> storage engine. The exception to
142 this rule is when the rowid table declares an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>.
143 In the exception, the INTEGER PRIMARY KEY becomes an alias for the
144 <a href="lang_createtable.html#rowid">rowid</a>.
145
146 <li><p>
147 The true primary key for a rowid table (the value that is used as the
148 key to look up rows in the underlying <a href="fileformat2.html#btree">B-tree</a> storage engine)
149 is the <a href="lang_createtable.html#rowid">rowid</a>.
150
151 <li><p>
152 The PRIMARY KEY constraint for a rowid table (as long as it is not
153 the true primary key or INTEGER PRIMARY KEY) is really the same thing
154 as a <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a>. Because it is not a true primary key,
155 columns of the PRIMARY KEY are allowed to be NULL, in violation of
156 all SQL standards.
157
158 <li><p>
159 The <a href="lang_createtable.html#rowid">rowid</a> of a rowid table can be accessed (or changed) by reading or
160 writing to any of the "rowid" or "oid" or "_rowid_" columns. Except,
161 if there are declared columns in the table that use any of those
162 special names, then those names refer to the declared columns, not to
163 the underlying <a href="lang_createtable.html#rowid">rowid</a>.
164
165 <li><p>
166 Access to records via <a href="lang_createtable.html#rowid">rowid</a> is highly optimized and very fast.
167
168 <li><p>
169 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
170 persistent and might change. In particular the <a href="lang_vacuum.html">VACUUM</a> command will
171 change rowids for tables that do not declare an INTEGER PRIMARY KEY.
172 Therefore, applications should not normally access the rowid directly,
173 but instead use an INTEGER PRIMARY KEY.
174
175 <li><p>
176 In the underlying <a href="fileformat2.html">file format</a>, each rowid is stored as a
177 <a href="fileformat2.html#varint">variable-length integer</a>. That means that small non-negative
178 rowid values take up less disk space than large or negative
179 rowid values.
180
181 <li><p>
182 All of the complications above (and others not mentioned here)
183 arise from the need to preserve backwards
184 compatibility for the hundreds of billions of SQLite database files in
185 circulation. In a perfect world, there would be no such thing as a "rowid"
186 and all tables would following the standard semantics implemented as
187 <a href="withoutrowid.html">WITHOUT ROWID</a> tables, only without the extra "WITHOUT ROWID" keywords.
188 Unfortunately, life is messy. The designer of SQLite offers his
189 sincere apology for the current mess.
190 </ul>
191 <p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
192