Documentation Source Text

Artifact [d768bc62ed]

Artifact d768bc62edee4f8422792313cbd5d80d5acdeeb340cd5472c7925c5a91ff8cd4:

<title>Rowid Tables</title>
hd_keywords {rowid table} {rowid tables}
<h1 align="center">Rowid Tables</h1>

<h2>1.0 Definition</h2>

<p>A "rowid table" is any table in an SQLite schema that
<li>is <em>not</em> a [virtual table], and
<li>is <em>not</em> a [WITHOUT ROWID] table.
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>

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 

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

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.

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

Access to records via [rowid] is highly optimized and very fast.

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.

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.

All of the complications above (and others not mentioned here)
arise from the need to preserve backwards
compatibility for the hundreds 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.