Documentation Source Text

has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.
</p>

<p>
^SQLite keeps track of the largest ROWID that a table has ever held using
an [internal table] named "[sqlite_sequence]".
^The sqlite_sequence table is created
and initialized automatically whenever a normal table that contains an
AUTOINCREMENT column is created.  ^The content of the sqlite_sequence table
can be modified using ordinary UPDATE, INSERT, and DELETE statements.
^But making modifications to this table will likely perturb the AUTOINCREMENT
key generation algorithm.  Make sure you know what you are doing before
you undertake such changes.
</p>

<p>

<p>During a transaction, SQLite stores additional information 
in a second file called the "rollback journal", or if SQLite is in
[WAL mode], a write-ahead log file.
If the application or
host computer crashes before the transaction completes, then the rollback
journal or write-ahead log contains critical state information needed 
to restore the main database file to a consistent state.  When a rollback 
journal or write-ahead log contains information necessary for recovering 
the state of the database, they are called a "hot journal" or "hot WAL file".
Hot journals and WAL files are only a factor during error recovery
scenarios and so are uncommon, but they are part of the state of an SQLite
database and so cannot be ignored.  This document defines the format
of a rollback journal and the write-ahead log file, but the focus is
on the main database file.</p>

are unique.</p>

<p>^There is a one-to-one mapping between rows in a table and
entries in each index associated with that table.
^Corresponding rows int the index and table b-trees share the same rowid
value, and contain the same value for all indexed columns.</p>

<tcl>hd_fragment sqlite_master {sqlite_master} {sqlite_master table}</tcl>
<h3>2.5 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>

<blockquote><pre>
CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
</pre></blockquote>)^

<p>^The sqlite_master table contains one row for each table, index, view,
and trigger (collectively "objects") in the database schema, except there
is no entry for the sqlite_master table itself.  ^The sqlite_master table
contains entries for [internal schema objects] in addition to application-
and programmer-defined objects.


<p>^(The sqlite_master.type column will be one
of the following text strings:  'table', 'index', 'view', or 'trigger'
according to the type of object defined.  The 'table' string is used
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.)^</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>

<li>^Leading spaces are removed.
<li>^All spaces following the first two keywords are converted into a single
space.
</ul>

<p>^(The text in the sqlite_master.sql column is a copy of the original
CREATE statement text that created the object, except normalized as
described above and as modified by subsequent [ALTER TABLE] statements.)^
^(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.5.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
whose name begins with "sqlite_" is an internal schema object.
^SQLite prohibits applications from creating objects whose names begin
with "sqlite_".  

<p>Internal schema objects used by SQLite may include the following:

<ul>
<li><p>Indices with names of the form "sqlite_autoindex_TABLE_N" that
       are used to implement [UNIQUE] and [PRIMARY KEY] constraints on
       ordinary tables.

<li><p>A table with the name "sqlite_sequence" that is used to keep track
       of the maximum historical [INTEGER PRIMARY KEY] for a table that
       using [AUTOINCREMENT].

<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>Additional 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.5.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:

<blockquote><pre>
CREATE TABLE sqlite_sequence(name,seq);
</pre></blockquote>

<p>^There is a single row in the sqlite_sequence table for each ordinary
table that uses AUTOINCREMENT.  The name of the table (as it appears in
sqlite_master.name) is in the sqlite_sequence.main field and the largest
[INTEGER PRIMARY KEY] ever used by that table is in the sqlite_sequence.seq
field.  ^New automatically generated integer primary keys for AUTOINCREMENT
tables are guaranteed to be larger than the sqlite_sequence.seq field for
that table.
^If the sqlite_sequence.seq field of an AUTOINCREMENT table is already at
the largest integer value (9223372036854775807) then attempts to add new
rows to that table with an automatically generated integer primary will fail
with an [SQLITE_FULL] error.
^The sqlite_sequence.seq field is automatically updated if required when
new entries are added to an AUTOINCREMENT table.  
^The sqlite_sequence row for an AUTOINCREMENT table is automatically deleted
when the table is dropped.
^If the sqlite_sequence row for an AUTOINCREMENT table does not exist when
the AUTOINCREMENT table is updated, then a new sqlite_sequence row is created.
^If the sqlite_sequence.seq value for an AUTOINCREMENT table is manually 
set to something other than an integer and there is a subsequent attempt to
insert the or update the AUTOINCREMENT table, then the behavior is undefined.

<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}</tcl>
<h4>2.5.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:

<blockquote><pre>
CREATE TABLE sqlite_stat1(tbl,idx,stat);
</pre></blockquote>

<p>There is normally one row per index, with the index identified by the
name in the sqlite_stat1.idx column.  The sqlite_stat1.tbl column is
the name of the table to which the index belongs.  In each such row, 
the sqlite_stat.stat column will be
a string consisting of a list of integers.  The first integer in this
list is the number of rows in the index and in the table.  The second
integer is the average number of rows in the index that have the same
value in the first column of the index.  The third integer is the average
number of rows in the index that have the same value for the first two
columns.  The N-th integer (for N>1) is the average number of rows in 
the index which have the same value for the first N-1 columns.  For
a K-column index, there will be K+1 integers in the stat column.  If
the index is unique, then the last integer will be 1.

<p>The list of integers in the stat column can optionally be followed
by the keyword "unordered".  The "unordered" keyword, if it is present,
must be separated from the last integer by a single space.  If the
"unordered" keyword is present, then the query planner assumes that
the index is unordered and will not use the index for a range query.

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

<tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
<h4>2.5.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.
The schema of the sqlite_stat2 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);
</pre></blockquote>

<p>The sqlite_stat2.idx column and the sqlite_stat2.tbl column in each 
row of the sqlite_stat2 table identify an index described by that row.
There are usually 10 rows in the sqlite_stat2
table for each index.

<p>The sqlite_stat2 entries for an index that have sqlite_stat2.sampleno
between 0 and 9 inclusive are samples of the left-most key value in the
index taken at evenly spaced points along the index.
Let C be the number of rows in the index.
Then the sampled rows are given by

<blockquote>
     rownumber = (i*C*2 + C)/20
</blockquote>

<p>The variable i in the previous expression varies between 0 and 9.
Conceptually, the index space is divided into
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}</tcl>
<h4>2.5.5 The sqlite_stat3 table</h4>

<p>The sqlite_stat3 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT3] 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.6.9.
The sqlite_stat3 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat3 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>

<p>There are usually multiple entries in the sqlite_stat3 table for each index.
The sqlite_stat3.sample column holds the value of the left-most field of an
index identified by the sqlite_stat3.idx and the sqlite_stat3.tbl columns.
If the sqlite_stat3.idx and sqlite_stat3.tbl
columns hold the same value, then that row contains a sample from
the [INTEGER PRIMARY KEY] of the table.
The sqlite_stat3.nEq column holds the approximate
number of entries in the index whose left-most column exactly matches
the sample.  
The sqlite_stat3.nLt holds the approximate number of entries in the
index whose left-most column is less than the sample.
The sqlite_stat3.nDLt column holds the approximate
number of distinct left-most entries in the index that are less than
the sample.

<p>Future versions of SQLite might change to store a string containing
multiple integers values in the sqlite_stat3.nDLt column, a string in which
the first integer will be the number of prior index entries that are
distinct in the left-most column, the second integer is the number of
prior index entries that are distinct in the first two columns, the
third integer is the number of prior index entries that are distinct
in the first three columns, and so forth  With such an extension, the
nDLt field will become similar in function to the sqlite_stat1.stat field.

<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 rollbackjournal {rollback journal format}</tcl>
<h2>3.0 The Rollback Journal</h2>

<p>The rollback journal is a file associated with each SQLite database
file that hold information used to restore the database file to its initial
state during the course of a transaction.

Section {ANALYZE} analyze ANALYZE

BubbleDiagram analyze-stmt 1
</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in a [internal tables] in the database where the query optimizer can use
them to help make better query planning choices.
^If no arguments are given, all attached databases are
analyzed.  ^If a database name is given as the argument, then all tables
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named "[sqlite_stat1]".  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT3] option, then additional histogram data is
collected and stored in [sqlite_stat3].
Older versions of SQLite would make use of the [sqlite_stat2] table
when compiled with [SQLITE_ENABLE_STAT2] but all recent versions of
SQLite ignore the sqlite_stat2 table.
Future enhancements may create
additional [internal tables] with the same name pattern except with
final digit larger than "3".</p>

<p> ^The [ALTER TABLE] command does
not work on the sqlite_stat1 or sqlite_stat3 tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
^(The [DROP TABLE] command works on sqlite_stat1 and
sqlite_stat3 as of SQLite version 3.7.9.)^
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.</p>

<p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as
the content of the database changes.  If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>

<p> The query planner might not notice manual changes to the
sqlite_stat1 and/or sqlite_stat3 tables.  ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<tcl>
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^(The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<tcl>hd_fragment uniqueidx {unique index}</tcl>
<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguous) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and

compile-time parameter. ^A single row of a table cannot store more than
[SQLITE_MAX_LENGTH] bytes of data. ^Both of these limits can be lowered at
runtime using the [sqlite3_limit()] C/C++ interface.</p>

<tcl>hd_fragment {constraints} {constraints}</tcl>
<h3>SQL Data Constraints</h3>

<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.
  ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE
  statement, it is an error.

  conform to the standard (and we might do so in the future), but by the time
  the oversight was discovered, SQLite was in such wide use that we feared
  breaking legacy code if we fixed the problem.  So for now we have chosen to
  continue allowing NULLs in PRIMARY KEY columns.  Developers should be
  aware, however, that we may change SQLite to conform to the SQL standard in
  future and should design new programs accordingly.

<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. ^As with
  PRIMARY KEY constraints, for the purposes of UNIQUE constraints 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

  violation has occurred.)^ ^If the CHECK expression evaluates to NULL, or
  any other non-zero value, it is not a constraint violation.
  ^The expression of a CHECK constraint may not contain a subquery.

<p>CHECK constraints have been supported since [version 3.3.0]. Prior to
  version 3.3.0, CHECK constraints were parsed but not enforced.

<tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}</tcl>
<p>^A <b>NOT NULL</b> constraint may only be attached to a column definition,
  not specified as a table constraint.  Not surprisingly, ^(a NOT NULL
  constraint dictates that the associated column may not contain a NULL value.
  Attempting to set the column value to NULL when inserting a new row or
  updating an existing one causes a constraint violation.)^

<p>Exactly how a constraint violation is dealt with is determined by the