Documentation Source Text

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 or on any
table column other than INTEGER PRIMARY KEY.  ^Any attempt to use 
AUTOINCREMENT on a [WITHOUT ROWID] table or on a column other than the
INTEGER PRIMARY KEY column results in an error.</p>

  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2013-12-?? (3.8.2)} {
<li>Added support for [WITHOUT ROWID] tables.
<li>Extended the [virtual table] interface, and in particular the
    [sqlite3_index_info] object to allow a virtual table to report its estimate
    on the number of rows that will be returned by a query.
<li>Update the [R-Tree extension] to make use of the enhanced virtual table
    interface.
}

chng {2013-10-17 (3.8.1)} {
<li>Added the [unlikely()] and [likelihood()] SQL functions to be used
    as hints to the query planner.
<li>Enhancements to the query planner:
<ul>

  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
  the table is a [WITHOUT ROWID] table or the column is declared NOT NULL,
  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 contain 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 

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

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