Documentation Source Text

Artifact Content
Login

Artifact d09bc74b7c489b8e1884d4bdaf9eb4508dc53581:


<title>Rowid Tables</title>
<tcl>
hd_keywords {rowid table} {rowid tables}
</tcl>
<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 [virtual table], and
<li>is <em>not</em> a [WITHOUT ROWID] 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 [rowid] that is used as
the access key for the data in the underlying [B-tree] storage engine.

<h2>2.0 Quirks</h2>

<ul>
<li><p>
The [PRIMARY KEY] 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 [B-tree] storage engine.  The exception to
this rule is when the rowid table declares an [INTEGER PRIMARY KEY].
In the exception, the INTEGER PRIMARY KEY becomes an alias for the 
[rowid].

<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 [B-tree] storage engine)
is the [rowid].

<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 [UNIQUE constraint].  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 [rowid] 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 [rowid].

<li><p>
Access to records via [rowid] is highly optimized and very fast.

<li><p>
If the [rowid] is not aliased by [INTEGER PRIMARY KEY] then it is not
persistent and might change.  In particular the [VACUUM] 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 [file format], each rowid is stored as a
[variable-length integer].  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
[WITHOUT ROWID] 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>