Artifact d768bc62edee4f8422792313cbd5d80d5acdeeb340cd5472c7925c5a91ff8cd4:
- File pages/rowidtable.in — part of check-in [c34168d55b] at 2018-04-12 15:31:42 on branch trunk — There are "hundreds of billions", not "tens of billions" of SQLite database files in circulation - in the rowidtable.html document. (user: drh size: 2843)
<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 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. </ul>