Documentation Source Text

<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>

<p>
^(You can access the ROWID of an SQLite table using one 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

<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 [WITHOUT ROWID] tables.  Any
attempt to use AUTOINCREMENT on a [WITHOUT ROWID] table results in an
error.</p>

<h4>1.2.15 Application ID</h4>

<p>^The 4-byte big-endian integer at offset 68 is an "Application ID" that
can be set by the [PRAGMA application_id] command in order to identify the
database as belonging to or associated with a particular application.
The application ID is intended for database files used as an
[application file-format].  The application ID can be used by utilities 
such as [http://www.darwinsys.com/file/ | file(1)] to determine the specific
file type rather than just reporting "SQLite3 Database".  A list of
assigned application IDs can be seen by consulting the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt|magic.txt]
file in the SQLite source repository.</p>

<tcl>hd_fragment validfor {version-valid-for number}</tcl>
<h4>1.2.16 Write library version number and version-valid-for number</h4>

<p>^The number of freelist pages is stored as a 4-byte big-endian integer
in the database header at an offset of 36 from the beginning of the file.
^The database header also stores the page number of the first freelist trunk
page as a 4-byte big-endian integer at an offset of 32 from the beginning
of the file.</p>

<tcl>hd_fragment btree {B-Trees} {B*-Trees}</tcl>
<h3>1.5 B-tree Pages</h3>

<p>The b-tree algorithm provides key/data storage with unique and
ordered keys on page-oriented storage devices.
For background information on b-trees, see
Knuth, <u>The Art Of Computer Programming</u>, Volume 3 "Sorting
and Searching", pages 471-479.  Two kinds of b-trees are used by
SQLite.  The algorithm that Knuth calls "B*-Tree" stores all data
in the leaves of the tree.  SQLite calls this variety of b-tree
a "table b-tree". The algorithm that Knuth calls simply "B-Tree"
stores both the key and the data together in both leaves
and in interior pages.  In the SQLite implementation, the original
B-Tree algorithm stores keys only, omitting the data entirely, and
is called an "index b-tree".

<p>A b-tree page is either an interior page or a leaf page.
A leaf page contains keys and in the case of a table b-tree each
key has associated data.  An interior page contains
K keys together with K+1 pointers to child b-tree pages.
A "pointer" in an interior b-tree page is just the 31-bit integer
page number of the child page.</p>


<p>Define the depth
of a leaf b-tree to be 1 and the depth of any interior b-tree to be one
more than the maximum depth of any of its children.  ^In a well-formed
database, all children of an interior b-tree have the same depth.</p>

<p>In an interior b-tree page, the pointers and keys logically alternate 
with a pointer on both ends. (The previous sentence is to be understood
conceptually - the actual layout of the keys and
pointers within the page is more complicated and will be described in
the sequel.)  All keys within the same page are unique and are logically
organized in ascending order from left to right.  (Again, this ordering
is logical, not physical.  The actual location of keys within the page
is arbitrary.) ^For any key X, pointers to the left
of a X refer to b-tree pages on which all keys are less than or equal to X.
^Pointers to the right of X refer to pages where all keys are 
greater than X.</p>

<p>Within an interior b-tree page, each key and the pointer to its
immediate left are combined into a structure called a "cell".  The
right-most pointer is held separately.  A leaf b-tree page has no
pointers, but it still uses the cell structure to hold keys for
index b-trees or keys and content for table b-trees.  Data is also
contained in the cell.
</p>

<p>Every b-tree page has at most one parent b-tree page.
A b-tree page without a parent is called a root page.  A root b-tree page
together with the closure of its children form a complete b-tree.
It is possible (and in fact rather common) to have a complete b-tree
that consists of a single page that is both a leaf and the root.
Because there are pointers from parents to children, every page of a
complete b-tree can be located if only the root page is known.  Hence,
b-trees are identified by their root page number.</p>

<p>A b-tree page is either a table b-tree page or an index b-tree page.
All pages within each complete b-tree are of the same type: either table
or index.  There is a one table b-trees in the database file
for each rowid table in the database schema, including system tables
such as sqlite_master.  There is one index b-trees
in the database file for each index in the schema, including implied indices
created by uniqueness constraints.  There are no b-trees associated with
[virtual tables].  Specific virtual table implementations might make use
of [shadow tables] for storage, but those shadow tables will have separate
entries in the database schema.  [WITHOUT ROWID] tables use an index b-tree
as their princpal storage rather than a table b-tree, so there is one
index b-tree in the database file for each [WITHOUT ROWID] table.
The b-tree corresponding to the sqlite_master table is always a table
b-tree and always has a root page of 1.
The sqlite_master table contains the root page number for every other 
table and index in the database file.</p>

<p>Each entry in a table b-tree consists of a 64-bit signed integer key
and up to 2147483647 bytes of arbitrary data.  (The key of a table b-tree
corresponds to the [rowid] of the SQL table that the b-tree implements.)
Interior table b-trees hold only keys and pointers to children.
All data is contained in the table b-tree leaves.</p>

<p>Each entry in an index b-tree consists of an arbitrary key of up
to 2147483647 bytes in length and no data.</p>

<tcl>hd_fragment cell_payload {cell payload}</tcl>
<p>Define the "payload" of a cell to be the arbitrary length section
of the cell.  For an index b-tree, the key is always arbitrary in length

order of appearance, for the various b-tree page types.</p>

<blockquote><dl>
<dt><p>Table B-Tree Leaf Cell:</p></dt>
<dd><p><ul>
<li>A varint which is the total number of bytes of payload, including any
overflow
<li>A varint which is the integer key, a.k.a. "[rowid]"
<li>The initial portion of the payload that does not spill to overflow
pages.
<li>A 4-byte big-endian integer page number for the first page of the
overflow page list - omitted if all payload fits on the b-tree page.
</ul></p></dd>

<dt><p>Table B-Tree Interior Cell:</p></dt>

accessing a large data set.  This section will describe how the
low-level b-tree layer is used to implement higher-level SQL
capabilities.</p>

<tcl>hd_fragment record_format {record format}</tcl>
<h3>2.1 Record Format</h3>

<p>The data for a table b-tree leaf page and the key
of an index b-tree page was characterized above
as an arbitrary sequence of bytes.
The prior discussion mentioned one key being less than another, but
did not define what "less than" meant.  The current section will address
these omissions.</p>

<p>Payload, either table b-tree data or index b-tree keys, 
is always in the "record format".
The record format defines a sequence of values corresponding
to columns in a table or index.  The record format specifies the number
of columns, the datatype of each column, and the content of each column.</p>

<p>The record format makes extensive use of the 
[variable-length integer] or [varint]
representation of 64-bit signed integers defined above.</p>

^When a column is indexed, the same collating function specified in the
[CREATE TABLE] statement is used for the column in the index, by default,
though this can be overridden using a COLLATE clause in the 
[CREATE INDEX] statement.

<h3>2.3 Representation Of SQL Tables</h3>

<p>Each ordinary SQL table in the database schema is represented on-disk
by a table b-tree.  Each entry in the table b-tree corresponds to a row
of the SQL table.  The [rowid] of the SQL table is the 64-bit signed
integer key for each entry in the table b-tree.</p>

<p>The content of each SQL table row is stored in the database file by
first combining the values in the various columns into a byte array
in the record format, then storing that byte array as the payload in

<p>^If the [affinity] of a column is REAL and that column contains a
value that can be converted to an integer without loss of information
(if the value contains no fractional part and is not too large to be
represented as an integer) then the column may be stored in the record
as an integer.  ^SQLite will convert the value back to floating
point when extracting it from the record.</p>

<h3>2.4 Representation of WITHOUT ROWID Tables</h3>

<p>If an SQL table is created using the "WITHOUT ROWID" clause at the
end of its CREATE TABLE statement, then that table is a [WITHOUT ROWID]
table and uses a different on-disk representation.  A WITHOUT ROWID
table uses an index b-tree rather than a table b-tree for storage.
The key for each entry in the WITHOUT ROWID b-tree is a record composed
of the columns of the PRIMARY KEY followed by all remaining columns of
the table.  The primary key columns appear in the order they they were
declared in the PRIMARY KEY clause and the remaining columns appear in
the order they occur in the CREATE TABLE statement.

<p>^Hence, the content encoding for a WITHOUT ROWID table is the same
as the content encoding for an ordinary rowid table, except that the
order of the columns is rearranged so that PRIMARY KEY columns come
first, and the content is used as the key in an index b-tree rather
than as the data in a table b-tree.
^The special encoding rules for columns with REAL affinity
apply to WITHOUT ROWID tables the same as they do with rowid tables.

<h3>2.5 Representation Of SQL Indices</h3>

<p>^Each SQL index, whether explicitly declared via a [CREATE INDEX] statement
or implied by a UNIQUE or PRIMARY KEY constraint, corresponds to an 
index b-tree in the database file.
^Each entry in the index b-tree corresponds to a single row in the 
associated SQL table.
^The key to an index b-tree is
a record composed of the columns that are being indexed followed by the
key of the corresponding table row.  For ordinary tables, the row key is
the [rowid], and for [WITHOUT ROWID] tables the row key is the PRIMARY KEY.
Because every row in the table has a unique row key,

all keys in an index are unique.</p>

<p>^In a normal index, there is a one-to-one mapping between rows in a 
table and entries in each index associated with that table.
^However, in a [partial index], the index b-tree only contains entries
corresponding to table rows for which the WHERE clause expression on the
CREATE INDEX statement is true.
^Corresponding rows in the index and table b-trees share the same rowid
or primary key values and contain the same value for all indexed columns.</p>

<h4>2.5.1 Suppression of redundant columns in WITHOUT ROWID secondary indexed
</h4>

<p>In an index on a WITHOUT ROWID table, if one or more of the columns
of table PRIMARY KEY are also columns of the index, then the
indexed column is not repeated in the table-key suffix on the end of
the index record.  As an example, consider the following SQL:

<blockquote><pre>
CREATE TABLE ex25(a,b,c,d,e,PRIMARY KEY(d,c,a)) WITHOUT rowid;
CREATE INDEX ex25ce ON ex25(c,e);
CREATE INDEX ex25acde ON ex25(a,c,d,e);
</pre></blockquote>

<p>Each row in the ex25ce index of the example above consists of a record
containing the following columns: c, e, d, a.  The first two columns are
the columns being indexed, c and e.  The remaining columns are the primary
key of the corresponding table row.  Normally, the primary key would be
columns d, c, and a, but because column c already appears earlier in the
index, it is omitted from the key suffix.</p>

<p>In the extreme case where the columns being indexed cover all columns
of the PRIMARY KEY, the index will consist of only the columns being
indexed.  The ex25acde example above demonstrates this.  Each entry in
the ex25acde index consists of only the columns a, c, d, and e, in that
order.</p>

<p>The suppression of redundant columns in the key suffix of an index
entry only occurs in WITHOUT ROWID tables.  In an ordinary rowid table,
the index entry always ends with the rowid even if the [INTEGER PRIMARY KEY]
column is one of the columns being indexed.</p>

<tcl>hd_fragment sqlite_master {sqlite_master} {sqlite_master table}</tcl>
<h3>2.6 Storage Of The SQL Database Schema</h3>

<p>^Page 1 of a database file is the root page of a table b-tree that
holds a special table named "sqlite_master" (or "sqlite_temp_master" in
the case of a TEMP database) which stores the complete
database schema.  ^(The structure of the sqlite_master table is as
if it had been created using the following SQL:</p>

for both ordinary and [virtual tables].)^</p>

</p>^(The sqlite_master.name column will hold the name of the object.)^
^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create
[internal indices] with names of the form "sqlite_autoindex_TABLE_N"
where TABLE is replaced by the name of the table that contains the
constraint and N is an integer beginning with 1 and increasing by one
with each constraint seen in the table definition.)^
^(In a [WITHOUT ROWID] table, there is no sqlite_master entry for the
PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside
for the PRIMARY KEY as if the sqlite_master entry did exist.  This
will affect the numbering of subsequent UNIQUE constraints.)^
^The "sqlite_autoindex_TABLE_N" name is never allocated for an
[INTEGER PRIMARY KEY], either in rowid tables or WITHOUT ROWID tables.
</p>

<p>The sqlite_master.tbl_name column holds the name of a table or view
that the object is associated with.  ^For a table or view, the
tbl_name column is a copy of the name column.  ^For an index, the tbl_name
is the name of the table that is indexed.  ^For a trigger, the tbl_name
column stores the name of the table or view that causes the trigger 
to fire.</p>

^(The sqlite_master.sql is NULL for the [internal indices] that are
automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p>


<tcl>hd_fragment intschema {internal schema objects} \
{internal schema object} {internal index} {internal indices} \
{internal table} {internal tables}</tcl>
<h4>2.6.1 Internal Schema Objects</h4>

<p>^In addition to the tables, indices, views, and triggers created by
the application and/or the developer using CREATE statements SQL, the
sqlite_master table may contain zero or more entries for 
<i>internal schema objects</i> that are created by SQLite for its 
own internal use.  ^The names of internal schema objects
always begin with "sqlite_" and any table, index, view, or trigger

<li><p>Tables with names of the form "sqlite_statN" where N is an integer.
       Such tables store database statistics gathered by the [ANALYZE]
       command and used by the query planner to help determine the best
       algorithm to use for each query.
</ul>

<p>New internal schema objects names, always beginning with "sqlite_",
may be added to the SQLite file format in future releases.

<tcl>hd_fragment seqtab {sqlite_sequence}</tcl>
<h4>2.6.2 The sqlite_sequence table</h4>

<p>^The sqlite_sequence table is an internal table used to help implement
[AUTOINCREMENT].  ^The sqlite_sequence table is created automatically
whenever any ordinary table with an AUTOINCREMENT integer primary
key is created.  ^Once created, the sqlite_sequence table exists in the
sqlite_master table forever; it cannot be dropped.
The schema for the sqlite_sequence table is:

<p>^Application code is allowed to modify the sqlite_sequence table, to add
new rows, to delete rows, or to modify existing rows.  ^However, application
code cannot create the sqlite_sequence table if it does not already exist.
^Application code can delete all entries from the sqlite_sequence table,
but application code cannot drop the sqlite_sequence table.

<tcl>hd_fragment stat1tab {sqlite_stat1} SQLITE_STAT1 </tcl>
<h4>2.6.3 The sqlite_stat1 table</h4>

<p>^The sqlite_stat1 is an internal table created by the [ANALYZE] command
and used to hold supplemental information about tables and indices that the
query planner can use to help it find better ways of performing queries.
^Applications can update, delete from, insert into or drop the sqlite_stat1
table, but may not create or alter the sqlite_stat1 table.
^The schema of the sqlite_stat1 table is as follows:

to help it choose smaller tables and indices that require less disk I/O.

<p>If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
column contains a single integer which is the approximate number of
rows in the table identified by sqlite_stat1.tbl.

<tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
<h4>2.6.4 The sqlite_stat2 table</h4>

<p>The sqlite_stat2 is only created and is only used if SQLite is compiled
with SQLITE_ENABLE_STAT2 and if the SQLite version number is between
3.6.18 and 3.7.8.  The sqlite_stat2 table is neither read nor written by any
version of SQLite before 3.6.18 nor after 3.7.8.
The sqlite_stat2 table contains additional information
about the distribution of keys within an index.

10 uniform buckets and the samples are the middle row from each bucket.

<p>The format for sqlite_stat2 is recorded here for legacy reference.  
Recent versions of SQLite no longer support sqlite_stat2 and the
sqlite_stat2 table, it is exists, is simply ignored.

<tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl>
<h4>2.6.5 The sqlite_stat3 table</h4>

<p>The sqlite_stat3 is only only used if SQLite is compiled
with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4]
and if the SQLite version number is 3.7.9 or greater.
The sqlite_stat3 table is neither read nor written by any
version of SQLite before 3.7.9.
If the [SQLITE_ENABLE_STAT4] compile-time option is used and the

<p>There can be an arbitrary number of sqlite_stat3 entries per index.
The [ANALYZE] command will typically generate sqlite_stat3 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl>
<h4>2.6.6 The sqlite_stat4 table</h4>

<p>The sqlite_stat4 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT4] and if the SQLite version number is
3.8.1 or greater.  The sqlite_stat4 table is neither read nor written by any
version of SQLite before 3.8.1.
The sqlite_stat4 table contains additional information
about the distribution of keys within an index, information that the

  <li><p> A default collation sequence to use with each column.

  <li><p> Optionally, a PRIMARY KEY for the table. Both single column and
       composite (multiple column) primary keys are supported.

  <li><p> A set of SQL constraints for each table. SQLite supports UNIQUE, NOT
       NULL, CHECK and FOREIGN KEY constraints.

  <li><p> Whether the table is a [WITHOUT ROWID] table.
</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with
  "sqlite_".)^

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^An error is rasied if more than one PRIMARY KEY clause appears in a
  CREATE TABLE statement.  ^The PRIMARY KEY is optional for ordinary tables
  but is required for [WITHOUT ROWID] tables.

<p>If a table has a single column primary key and the declared type of that
  column is "INTEGER" and the table is not a [WITHOUT ROWID] table,
  then the column is known as an [INTEGER PRIMARY KEY].
  See below for a description of the special properties and behaviors
  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must have a unique combination
  of values in its primary key columns. ^For the purposes of determining
  the uniqueness of primary key values, NULL values are considered distinct from
  all other values, including other NULLs. ^If an [INSERT] or [UPDATE]
  statement attempts to modify the table content so that two or more rows
  feature identical primary key values, it is a constraint violation.
  According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
  Unfortunately, due to a bug in some early versions, this is not the
  case in SQLite. ^Unless the column is an [INTEGER PRIMARY KEY] or unless
  the table is a [WITHOUT ROWID] table, SQLite
  allows NULL values in a PRIMARY KEY column.  SQLite could be fixed to
  conform to the standard, but doing so might break legacy applications.

  Hence, it has been decided to merely document the fact that SQLite
  allowing NULLs in most PRIMARY KEY columns.



<tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint}</tcl>
<p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must feature a unique combination
  of values in the columns identified by the UNIQUE constraint. 
  ^For the purposes of UNIQUE constraints, NULL values
  are considered distinct from all other values, including other NULLs.




<p>^In most cases, UNIQUE and PRIMARY KEY
  constraints are implemented by creating a unique index in the database.
  (The exceptions are [INTEGER PRIMARY KEY] and PRIMARY KEYs on 
  [WITHOUT ROWID] tables.)
  Hence, the following schemas are logically equivalent:

  <ol>
  <li><p>CREATE TABLE t1(a, b UNIQUE);
  <li><p>CREATE TABLE t1(a, b PRIMARY KEY);
  <li><p>CREATE TABLE t1(a, b);<br>

         CREATE UNIQUE INDEX t1b ON t1(b);
  </ol>

<tcl>hd_fragment {ckconst} {CHECK} {CHECK constraint} {CHECK constraints}</tcl>
<p>^(A <b>CHECK</b> constraint may be attached to a column definition or
  specified as a table constraint. In practice it makes no difference.)^ ^(Each
  time a new row is inserted into the table or an existing row is updated,
  the expression associated with each CHECK constraint is evaluated and
  cast to a NUMERIC value in the same way as a [CAST expression]. If the 

  conflict resolution algorithm is ABORT. ^Different constraints within the
  same table may have different default conflict resolution algorithms. See
  the section titled [ON CONFLICT] for additional information.

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>
<h3>ROWIDs and the INTEGER PRIMARY KEY</h3>

<p>^Except for [WITHOUT ROWID] tables, all rows within SQLite tables
have a 64-bit signed integer key that uniquely identifies the row within its table.
This integer is usually
called the "rowid". ^The rowid value can be accessed using one of the special
case-independent names "rowid", "oid", or "_rowid_" in place of a column name.
^If a table contains a user defined column named "rowid", "oid" or "_rowid_",
then that name always refers the explicitly declared column and cannot be used
to retrieve the integer rowid value.

<p>The rowid (and "oid" and "_rowid_") is omitted in [WITHOUT ROWID] tables.
WITHOUT ROWID tables are only available in SQLite [version 3.8.2] and later.
A table that lacks the WITHOUT ROWID clause is called a "rowid table".

<p>The data for rowid tables is stored as a B-Tree structure containing
one entry for each table row, using the rowid value as the key. This means that
retrieving or sorting records by rowid is fast. Searching for a record with a
specific rowid, or for all records with rowids within a specified range is
around twice as fast as a similar search made by specifying any other PRIMARY
KEY or indexed value.

<p> ^With one exception noted below, if a rowid table has a mary key that consists
of a single column, and the declared type of that column is "INTEGER" in any mixture of
upper and lower case, then the column becomes an alias for the rowid. Such a
column is usually referred to as an "integer primary key". A PRIMARY KEY column
only becomes an integer primary key if the declared type name is exactly
"INTEGER".  ^Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER"
or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary
table column with integer [affinity] and a unique index, not as an alias for
the rowid.

<p> The exception mentioned above is that ^if the declaration of a column with
declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not
become an alias for the rowid and is not classified as an integer primary key.
This quirk is not by design. It is due to a bug in early versions of SQLite.
But fixing the bug could result in backwards incompatibilities.
Hence, the original behavior has been retained (and documented) because
behavior in a corner case is far better than a compatibility break.  This means

that ^(the following three table declarations all cause the column "x" to be an
alias for the rowid (an integer primary key):

<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));</tt>

<title>The WITHOUT ROWID Optimization</title>
<tcl>
hd_keywords {WITHOUT rowid} {WITHOUT ROWID}
</tcl>
<h1 align="center">The WITHOUT ROWID Optimization</h1>

<h2>1.0 Introduction</h2>

<p>By default, every row in SQLite has a special column, usually called the
"[rowid]", that is a uniquely identifies that row within the table.  However
if the phrase "WITHOUT ROWID" is added to the end of a [CREATE TABLE] statement,
then the special "rowid" column is omitted.  There are sometimes
space and performance advantages to omitting the rowid.</p>

<h3>1.1 Syntax</h3>

<p>^(To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID"
to the end of the [CREATE TABLE] statement.  For example:</p>

<blockquote><pre>
CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) <b>WITHOUT ROWID</b>;
</pre></blockquote>)^

<p>^(As with all SQL syntax, the case of the keywords does not matter.  
One can write "WITHOUT rowid" or "without rowid" or "WiThOuT rOwId" and
it will mean the same thing.)^</p>

<p>Every WITHOUT ROWID table must have a [PRIMARY KEY].  ^An error is raised
if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.

<p>In most contexts, the special "rowid" column of normal tables can 
also be called "oid" or "_rowid_".  ^However, only "rowid" works as 
the keyword in the CREATE TABLE statement.</p>

<h3>1.2 Compatibility</h3>

<p>SQLite [version 3.8.2] or later is necessary in order to use a WITHOUT
ROWID table.  An attempt to open a database that contains one or more WITHOUT
ROWID tables using an earlier version of SQLite will result in a
"malformed database schema" error.</p>

<h2>2.0 Differences From Ordinary Rowid Tables</h2>

<p>The WITHOUT ROWID syntax is an optimization.  It provides no new
capabilities.  Anything that can be done using a WITHOUT ROWID table
can also be done in exactly the same way, and exactly the same syntax,
using an ordinary rowid table.  The only advantage of a WITHOUT ROWID
table is that it can sometimes use less disk space and/or perform a little
faster than an ordinary rowid table.</p>

<p>For the most part, ordinary rowid tables and WITHOUT ROWID tables
are interchangeable.  But there are some additional restrictions on
WITHOUT ROWID tables that do not apply to ordinary rowid tables:</p>

<ol>
<li><p>
<b>^Every WITHOUT ROWID table must have a PRIMARY KEY.</b>
^An attempt to create a WITHOUT ROWID table without a PRIMARY KEY results
in an error.

<li><p>
<b>^The special behaviors associated "[INTEGER PRIMARY KEY]" do not apply
on WITHOUT ROWID tables.</b>
In an ordinary table, "INTEGER PRIMARY KEY" means that the column is an
alias for the rowid.  But since there is no rowid in a WITHOUT ROWID
table, that special meaning no longer applies.  An "INTEGER PRIMARY KEY" works
like an "INT PRIMARY KEY" in an ordinary table: It is a PRIMARY KEY
that has integer [affinity].

<li><p>
<b>^[AUTOINCREMENT] does not work on WITHOUT ROWID tables.</b>
The [AUTOINCREMENT] mechanism assumes the presence of a rowid and so it
does not work on a WITHOUT ROWID table.  ^An error is raised if the
 "AUTOINCREMENT" keyword is used in the CREATE TABLE statement for
a WITHOUT ROWID table.

<li><p>
<b>^NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT
ROWID table.</b>
This is in accordance with the SQL standard.  Each column of a PRIMARY KEY
is supposed to be individually NOT NULL.  However, NOT NULL was not enforced
on PRIMARY KEY columns by early versions of SQLite due to a bug.  By the
time that this bug was discovered, so many SQLite databases were already
in circulation that the decision was made not to fix this bug for fear of
breaking compatibility.  ^So, ordinary rowid tables in SQLite violate the
SQL standard and allow NULL values in PRIMARY KEY fields.  ^But WITHOUT ROWID
tables do follow the standard and will throw an error on any attempt to
insert a NULL into a PRIMARY KEY column.

<li><p>
<b>^The [sqlite3_last_insert_rowid()] function
does not work for WITHOUT ROWID tables.</b>
Inserts into a WITHOUT ROWID do not change the value returned by the
[sqlite3_last_insert_rowid()] function.  The [last_insert_rowid()] SQL
function is also unaffected since it is just a wrapper around
[sqlite3_last_insert_rowid()].

<li><p>
<b>^The [sqlite3_blob_open | incremental blob I/O] mechanism does not work
for WITHOUT ROWID tables.</b>
Incremental BLOB I/O uses the rowid to create an [sqlite3_blob] object for
doing the direct I/O.  However, WITHOUT ROWID tables do not have a rowid,
and so there is no way to create an [sqlite3_blob] object for a WITHOUT
ROWID table.

<li><p>
<b>^The [sqlite3_update_hook()] interface does not fire callbacks for changes
to a WITHOUT ROWID table.</b>
Part of the callback from [sqlite3_update_hook()] is the rowid of the table
row that has changed.  However, WITHOUT ROWID tables do not have a rowid.
Hence, the update hook is not invoked when a WITHOUT ROWID table changes.
<p>Note that since the [session] extension uses the update hook, that means
that the session extension will not work correctly on a database that includes
WITHOUT ROWID tables.
</ol>

<h2>3.0 Benefits Of WITHOUT ROWID Tables</h2>

<p>A WITHOUT ROWID table is an optimization that can reduce storage and
processing requirements.

<p>In an ordinary SQLite table, the PRIMARY KEY is really just a 
[UNIQUE] index.  The key used to look up records on disk
is the [rowid].
The special "[INTEGER PRIMARY KEY]" column type in ordinary SQLite tables 
causes the column to be an alias for the rowid, and so an INTEGER PRIMARY
KEY is a true PRIMARY KEY.  But any other kind of PRIMARY KEYs, including
"INT PRIMARY KEY" are just unique indexes in an ordinary rowid table.</p>

<p>Consider a table (shown below) intended to store a
vocabulary of words together with a count of the number of occurrences of
each word in some text corpus:

<blockquote><pre>
CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
);
</pre></blockquote>

<p>As an ordinary SQLite table, "wordcount" is implemented as two
separate B-Trees.  The main table uses the hidden rowid value as the key
and stores the "word" and "cnt" columns as data.  The "TEXT PRIMARY KEY"
phrase of the CREATE TABLE statement
causes the creation of an [unique index] on the "word" column.  This index is a
separate B-Tree that uses "word" and the "rowid" as the key and stores no
data at all.  Note that the complete text of every "word" is stored twice:
once in the main table and again in the index.

<p>Consider querying this table to find the number of occurrences of the
word "xyzzy".:

<blockquote><pre>
SELECT cnt FROM wordcount WHERE word='xyzzy';
</pre></blockquote>

<p>This query first has to search the index B-Tree looking for any entry
that contains the matching value for "word".  When an entry is found in
the index, the rowid is extracted and used to search the main table.
Then the "cnt" value is read out of the main table and returned.  Hence, two
separate binary searches are required to fulfill the request.

<p>A WITHOUT ROWID table uses a different data design for the equivalent
table.

<blockquote><pre>
CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) WITHOUT ROWID;
</pre></blockquote>

<p>In this latter table, there is only a single B-Tree which uses the "word"
column as its key and the "cnt" column as its data.  (Technicality:  the
low-level implementation actually stores both "word" and "cnt" in the "key"
area of the B-Tree.  But unless you are looking at the low-level byte encoding
of the database file, that fact is unimportant.)  Because there is only
a single B-Tree, the text of the "word" column is only stored once in the
database.  Furthermore, querying the "cnt" value for a specific "word"
only involves a single binary search into the main B-Tree, since the "cnt"
value can be retrieved directly from the record found by that first search
and without the need to do a second binary search on the rowid.

<p>Thus, in some cases, a WITHOUT ROWID table can use about half the amount
of disk space and can operation nearly twice as fast.  Of course, in a 
real-world schema, there will typically be secondary indices and/or
UNIQUE constraints, and the situation is more complicated.  But even then,
there can often be space and performance advantages to using WITHOUT ROWID
on tables that have non-integer or composite PRIMARY KEYs.

<h2>4.0 When To Use WITHOUT ROWID</h2>

<p>The WITHOUT ROWID optimization is likely to be helpful for tables
that have non-integer or composite (multi-column) PRIMARY KEYs and that do
not store large strings or BLOBs.</p>

<p>WITHOUT ROWID tables will work correctly (that is to say, they
provide the correct answer) for tables with a single INTEGER PRIMARY KEY. 
However, ordinary rowid tables will run faster in that case.  
Hence, it is good design
to avoid creating WITHOUT ROWID tables with single-column PRIMARY KEYs 
of type INTEGER.

<p>WITHOUT ROWID tables work best when individual rows are not too large.
A good rule-of-thumb is that the average size of a single row in a
WITHOUT ROWID table should be less than about 1/20th the size of 
a database page.  That means that rows should not contain more than about
50 bytes each for a 1KiB page size or about 200 bytes each for 4KiB
page size.  WITHOUT ROWID tables will work (in the sense that
they get the correct answer) for arbitrarily large rows - up to 2GB in size -
but traditional rowid tables tend to work faster for large row sizes.
This is because rowid tables are implemented as [B*-Trees] where
all content is stored in the leaves of the tree, whereas WITHOUT ROWID 
tables are implemented using ordinary B-Trees with content stored on both
leaves and intermediate nodes.  Storing content in 
intermediate nodes mean that each intermediate node entry takes up more
space on the page and thus reduces the fan-out, increasing the search cost.

<p>Note that except for a few corner-case differences detailed above,
WITHOUT ROWID tables and rowid tables work the same.  They both generate
the same answers given the same SQL statements.  So it is a simple matter
to run experiments on an application, late in the development cycle,
to test whether or not the use of WITHOUT ROWID tables will be helpful.
A good strategy is to simply not worry about WITHOUT ROWID until near
the end of product development, then go back and run tests to see
if adding WITHOUT ROWID to tables with non-integer PRIMARY KEYs helps
or hurts performance, and retaining the WITHOUT ROWID only in those cases
where it helps.