Documentation Source Text

<tcl>BubbleDiagram sql-stmt</tcl>

<tcl>
proc Operator {name} {
  return "<font color=\"#2c2cf0\"><big>$name</big></font>"
}






proc Example {text} {
  hd_puts "<blockquote><pre>$text</pre></blockquote>"
}

proc Section {name label keywords} {
  global DOC
  hd_close_main
  hd_open_main lang_$label.html
  hd_header "SQLite Query Language: $name" $DOC/pages/lang.in
  eval hd_keywords $keywords
  if {[lsearch $keywords $name] == -1 && [lsearch $keywords *$name] == -1} {
    eval hd_keywords { $name }
  }
  hd_puts {<div class=nosearch>}
  hd_puts {<h1 align="center">SQL As Understood By SQLite</h1>}
  hd_puts {<p><a href="lang.html">[Top]</a></p>}
  hd_puts "<h2>$name</h2>"
  hd_puts {</div>}
}

























































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































##############################################################################
Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}}

RecursiveBubbleDiagram create-trigger-stmt
</tcl>

<p>^The CREATE TRIGGER statement is used to add triggers to the 

<title>ALTER TABLE</title>
<tcl>
hd_keywords {*altertable} {ALTER TABLE} {ALTER}
</tcl>

<fancy_format>

<h1>Overview</h1>

<tcl>
RecursiveBubbleDiagram alter-table-stmt
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table,
to rename a column within a table, or to add a new column to an existing table.

<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table} \
                 {ALTER TABLE RENAME documentation}</tcl>
<h1>ALTER TABLE RENAME</h1>

<p> ^(The RENAME TO syntax changes the name of <yyterm>table-name</yyterm>
to <yyterm>new-table-name</yyterm>.)^
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.
^If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.

<blockquote style='background-color: #ffd0d0;'>
<b>Compatibility Note:</b>
The behavior of ALTER TABLE when renaming a table was enhanced
in versions 3.25.0 ([dateof:3.25.0]) and 3.26.0 ([dateof:3.26.0])
in order to carry the rename operation forward into triggers and
views that reference the renamed table.  This is considered an
improvement. Applications that depend on the older (and
arguably buggy) behavior can use the
[PRAGMA legacy_alter_table=ON] statement or the
[SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] configuration parameter
on [sqlite3_db_config()] interface to make ALTER TABLE RENAME
behave as it did prior to version 3.25.0.
</blockquote>

<p>
Beginning with release 3.25.0 ([dateof:3.25.0]), references to the table
within trigger bodies and view definitions are also renamed.
</p>

<p> Prior to version 3.26.0 ([dateof:3.26.0]), FOREIGN KEY references
to a table that is renamed were only edited if the
[PRAGMA foreign_keys=ON], or in other words if
[foreign key constraints] were begin enforced.  With
[PRAGMA foreign_keys=OFF], FOREIGN KEY constraints would not be changed
when the table that the foreign key referred to (the "[parent table]")
was renamed.  Beginning with version 3.26.0, FOREIGN KEY constraints
are always converted when a table is renamed, unless the
[PRAGMA legacy_alter_table=ON] setting is engaged.  The following
table summaries the difference:</p>

<blockquote>
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<th>PRAGMA foreign_keys
<th>PRAGMA legacy_alter_table
<th>[parent table|Parent Table]
references are updated
<th>SQLite version
<tr><td align="center">Off<td align="center">Off<td align="center">No<td align="center">&lt; 3.26.0
<tr><td align="center">Off<td align="center">Off<td align="center">Yes<td align="center">&gt;= 3.26.0
<tr><td align="center">On<td align="center">Off<td align="center">Yes<td align="center">all
<tr><td align="center">Off<td align="center">On<td align="center">No<td align="center">all
<tr><td align="center">On<td align="center">On<td align="center">Yes<td align="center">all
</table>
</blockquote>

<tcl>hd_fragment altertabmvcol {ALTER TABLE RENAME COLUMN} {rename column}</tcl>
<h1>ALTER TABLE RENAME COLUMN</h1>

<p> The RENAME COLUMN TO syntax changes the
<yyterm>column-name</yyterm> of table <yyterm>table-name</yyterm>
into <yyterm>new-column-name</yyterm>.  The column name is changed both
within the table definition itself and also within all indexes, triggers,
and views that reference the column.  If the column name change would
result in a semantic ambiguity in a trigger or view, then the RENAME
COLUMN fails with an error and no changes are applied.

<tcl>hd_fragment altertabaddcol {ALTER TABLE ADD COLUMN} {add column}</tcl>
<h1>ALTER TABLE ADD COLUMN</h1>

<p> ^The ADD COLUMN syntax
is used to add a new column to an existing table.
^The new column is always appended to the end of the list of existing columns.
The [column-def] rule defines the characteristics of the new column.
^(The new column may take any of the forms permissible in a [CREATE TABLE]
statement, with the following restrictions:)^
<ul>
<li>^The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>^The column may not have a default value of CURRENT_TIME, CURRENT_DATE, 
    CURRENT_TIMESTAMP, or an expression in parentheses.</li>
<li>^If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
<li>^If [foreign key constraints] are [foreign_keys pragma | enabled] and
    a column with a [foreign-key-clause | REFERENCES clause]
    is added, the column must have a default value of NULL.
<li>^The column may not be [generated columns|GENERATED ALWAYS ... STORED],
though VIRTUAL columns are allowed.
</ul>

<p>^Note also that when adding a [CHECK constraint], the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p>The ALTER TABLE command works by modifying the SQL text of the schema
stored in the [sqlite_master table].
No changes are made to table content.
Because of this,
the execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 ([dateof:3.1.3]) and earlier.</p>

<tcl>hd_fragment otheralter {generalized ALTER TABLE procedure}</tcl>
<h1>Making Other Kinds Of Table Schema Changes</h1>

<p> The only schema altering commands directly supported by SQLite are the
"[rename table]", "[rename column]", and "[add column]" commands shown above.  
However, applications
can make other arbitrary changes to the format of a table using a simple
sequence of operations.
The steps to make arbitrary changes to the schema design of some table X
are as follows:

<ol>
<li><p>
If foreign key constraints are enabled, disable them using [PRAGMA foreign_keys=OFF].

<li><p>
Start a transaction.

<li><p>
Remember the format of all indexes, triggers, and views associated with table X.
This information will be needed in step 8 below.  One way to do this is
to run a query like the following:
SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.

<li><p>
Use [CREATE TABLE] to construct a new table "new_X" that is in the desired
revised format of table X.  Make sure that the name "new_X" does not collide
with any existing table name, of course.

<li><p>
Transfer content from X into new_X using a statement
like: INSERT INTO new_X SELECT ... FROM X.

<li><p>
Drop the old table X:  [DROP TABLE | DROP TABLE X].

<li><p>
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

<li><p>
Use [CREATE INDEX], [CREATE TRIGGER], and [CREATE VIEW]
to reconstruct indexes, triggers, and views
associated with table X.  Perhaps use the old format of the triggers,
indexes, and views saved from step 3 above as a guide, making changes
as appropriate for the alteration.

<li><p>If any views refer to table X in a way that is affected by the
schema change, then drop those views using [DROP VIEW] and recreate them
with whatever changes are necessary to accommodate the schema change
using [CREATE VIEW].

<li><p>
If foreign key constraints were originally enabled
then run [PRAGMA foreign_key_check] to verify that the schema
change did not break any foreign key constraints.


<li><p>
Commit the transaction started in step 2.

<li><p>
If foreign keys constraints were originally enabled, reenable them now.
</ol>

<tcl>hd_fragment caution</tcl>
<p>
<b>Caution:</b>
Take care to follow the procedure above precisely.  The boxes below
summarize two procedures for modifying a table definition.  At first
glance, they both appear to accomplish the same thing.  However, the
procedure on the right does not always work, especially with the
enhanced [rename table] capabilities added by versions 3.25.0 and
3.26.0.  In the procedure on the right, the initial rename of the
table to a temporary name might corrupt references to that table in
triggers, views, and foreign key constraints.  The safe procedure on
the left constructs the revised table definition using a new temporary
name, then renames the table into its final name, which does not break
links.

<center>
<table border="1" cellpadding="10" cellspacing="0">
<tr>
<td valign="top">
<ol>
<li>Create new table
<li>Copy data
<li>Drop old table
<li>Rename new into old
</ol>
<td valign="top">
<ol>
<li>Rename old table
<li>Create new table
<li>Copy data
<li>Drop old table
</ol>
<tr>
<th>&uarr;<br>Correct
<th>&uarr;<br>Incorrect
</table>
</center>

<p>The 12-step [generalized ALTER TABLE procedure]
above will work even if the
schema change causes the information stored in the table to change.
So the full 12-step procedure above is appropriate for dropping a column,
changing the order of columns, adding or removing a UNIQUE constraint
or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints,
or changing the datatype for a column, for example.  However, a simpler
and faster procedure can optionally be used for
some changes that do no affect the on-disk content in any way.
The following simpler procedure is appropriate for removing
CHECK or FOREIGN KEY or NOT NULL constraints,
or adding, removing, or changing default values on
a column.

<ol>
<lI><p> Start a transaction.

<li><p> Run [PRAGMA schema_version] to determine the current schema
version number.  This number will be needed for step 6 below.

<li><p> Activate schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=ON].

<li><p> Run an [UPDATE] statement to change the definition of table X
in the [sqlite_master table]: 
UPDATE sqlite_master SET sql=... WHERE type='table' AND name='X';
<p><em>Caution:</em>  Making a change to the sqlite_master table like this will
render the database corrupt and unreadable if the change contains
a syntax error.  It is suggested that careful testing of the UPDATE
statement be done on a separate blank database prior to using it on
a database containing important data.

<li><p> If the change to table X also affects other tables or indexes or
triggers are views within schema, then run [UPDATE] statements to modify
those other tables indexes and views too.  For example, if the name of
a column changes, all FOREIGN KEY constraints, triggers, indexes, and
views that refer to that column must be modified.
<p><em>Caution:</em>  Once again, making changes to the sqlite_master 
table like this will render the database corrupt and unreadable if the 
change contains an error.  Carefully test this entire procedure
on a separate test database prior to using it on
a database containing important data and/or make backup copies of
important databases prior to running this procedure.

<li><p> Increment the schema version number using
[PRAGMA schema_version | PRAGMA schema_version=X] where X is one
more than the old schema version number found in step 2 above.

<li><p> Disable schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=OFF].

<li><p> (Optional) Run [PRAGMA integrity_check] to verify that the
schema changes did not damage the database.

<li><p> Commit the transaction started on step 1 above.
</ol>

<p>If some future version of SQLite adds new ALTER TABLE capabilities, 
those capabilities will very likely use one of the two procedures
outlined above.

<tcl>hd_fragment altertableishard {why ALTER TABLE is so difficult}</tcl>
<h1>Why ALTER TABLE is such a problem for SQLite</h1>

<p>Most SQL database engines store the schema already parsed into
various system tables.  On those database engines, ALTER TABLE merely 
has to make modifications to the corresponding system tables.

<p>SQLite is different in that it stores the schema
in the [sqlite_master] table as the original text of the CREATE
statements that define the schema.  Hence ALTER TABLE needs
to revise the text of the CREATE statement.  Doing
so can be tricky for certain "creative" schema designs.

<p>The SQLite approach of storing the schema as text has advantages
for an embedded relational database.  For one, it means that the
schema takes up less space in the database file.  This is important
since a common SQLite usage pattern is to have many small,
separate database files instead of putting everything in one
big global database file, which is the usual approach for client/server
database engines.
Since the schema is duplicated in each separate database file, it is
important to keep the schema representation compact.

<p>Storing the schema as text rather than as parsed tables also
give flexibility to the implementation.  Since the internal parse
of the schema is regenerated each time the database is opened, the
internal representation of the schema can change from one release
to the next.  This is important, as sometimes new features require
enhancements to the internal schema representation.  Changing the
internal schema representation would be much more difficult if the
schema representation was exposed in the database file.  So, in other
words, storing the schema as text helps maintain backwards 
compatibility, and helps ensure that older database files can be
read and written by newer versions of SQLite.

<p>Storing the schema a text also makes the 
[SQLite database file format] easier to define, document, and 
understand. This helps make SQLite database files a
[recommended storage format] for long-term archiving of data.

<p>The downside of storing schema a text is that it can make
the schema tricky to modify.  And for that reason, the ALTER TABLE
support in SQLite has traditionally lagged behind other SQL
database engines that store their schemas as parsed system tables
that are easier to modify.



<tcl>
#  One of the reasons that
# SQLite does not currently support more ALTER TABLE capabilities is that
# the procedure shown above is difficult to automate for an arbitrary schema.
# Particularly troublesome areas are identifying all views associated with
# table X in step 1 and creating new views and triggers that are compatible
# with the altered schema for table X in step 6.  It is a tricky but solvable
# problem to create code that will perform the steps above 
# for "reasonable" schemas commonly found in practice.  
# But there exist malevolent schemas for which
# these steps are maddeningly difficult to do correctly.  Rather than create
# ALTER TABLE features that work correctly 99.9% of the time but possibly
# corrupt the schema on the other 0.1%, the SQLite developers have decided
# to push the problem into the application domain, where it is much easier
# to solve.
</tcl>

<title>ANALYZE</title>
<tcl>
hd_keywords {*analyze} {ANALYZE}
</tcl>

<fancy_format>
<h1>Overview</h1>

<tcl>
RecursiveBubbleDiagram analyze-stmt
</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in [internal tables] of the database where the query optimizer can
access the information and use it to help make better query planning choices.
^If no arguments are given, all attached databases are
analyzed.  ^If a schema 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>

<h1>Details</h1>

<p> ^The default implementation stores all statistics in a single
table named "[sqlite_stat1]".  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT3] option and without the [SQLITE_ENABLE_STAT4]
option, then additional histogram data is
collected and stored in [sqlite_stat3].
 ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT4] option, then additional histogram data is
collected and stored in [sqlite_stat4].
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 "4".
All of these tables are collectively referred to as "statistics tables".
</p>

<p> ^The content of the statistics tables can be queried using [SELECT]
and can be changed using the [DELETE], [INSERT], and [UPDATE] commands.
^(The [DROP TABLE] command works on statistics tables
as of SQLite version 3.7.9.)^ ([dateof:3.7.9])
^The [ALTER TABLE] command does not work on statistics tables.
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.  
See "[Manual Control Of Query Plans Using SQLITE_STAT Tables]" for
further information.</p>

<p> ^Statistics gathered by ANALYZE are not 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 loads the content of the statistics tables
into memory when the schema is read.  ^Hence, when an application
changes the statistics tables directly, SQLite will not immediately
notice the changes. ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<tcl>hd_fragment autoanalyze {automatically running ANALYZE}</tcl>
<h1>Automatically Running ANALYZE</h1>

<p>The [PRAGMA optimize] command will automatically run ANALYZE on individual
tables on an as-needed basis.  The recommended practice is for applications
to invoke the [PRAGMA optimize] statement just before closing each database
connection.</p>

<p>Each SQLite [database connection] records cases when the query planner would
benefit from having accurate results of ANALYZE at hand.  These records
are held in memory and accumulate over the life of a database connection.
The [PRAGMA optimize] command looks at those records and runs ANALYZE on only
those tables for which new or updated ANALYZE data seems likely to be useful.
In most cases [PRAGMA optimize] will not run ANALYZE, but it will occasionally
do so either for tables that have never before been analyzed, or for tables
that have grown significantly since they were last analyzed.</p>

<p>Since the actions of [PRAGMA optimize] are determined to some extent by
prior queries that have been evaluated on the same database connection, it
is recommended that [PRAGMA optimize] be deferred until the database connection
is closing and has thus had an opportunity to accumulate as much usage information
as possible.  It is also reasonable to set a timer to run [PRAGMA optimize]
every few hours, or every few days, for database connections that stay open
for a long time.</p>

<p>Applications that desire more control can run [PRAGMA optimize(0x03)] to 
obtain a list of ANALYZE commands that SQLite thinks are appropriate to run,
but without actually running those commands.  If the returned set is 
non-empty, the application can then make a decision about whether or not
to run the suggested ANALYZE commands, perhaps after prompting the user
for guidance.</p>

<p>The [PRAGMA optimize] command was first introduced with 
SQLite 3.18.0 ([dateof:3.18.0]) and is a no-op for all prior releases
of SQLite.</p>

<h1>Anticipated Future Enhancements</h1>

<p>All existing versions of SQLite do a full table scan for ANALYZE.  This can be
slow for multi-gigabyte and larger databases.  Future versions of SQLite might
use random sampling rather than a full table scan to obtain estimates for the
database shape, especially on larger tables.  The results would approximate, but 
will be close enough for query planning purposes.  As of 2017-03-20, this
concept has been tested in experimental branches and appears to work well, but
has not been folded into an official release.</p>

<title>ATTACH DATABASE</title>
<tcl>
hd_keywords attach attached *ATTACH {ATTACH DATABASE}
</tcl>

<fancy_format>
<h1>Overview</h1>
<tcl>
RecursiveBubbleDiagram attach-stmt
</tcl>

<p> ^The ATTACH DATABASE statement adds another database 
file to the current [database connection]. 
Database files that were previously attached can be removed using
the [DETACH DATABASE] command.

<h1>Details</h1>

<p>^The filename for the database to be attached is the value of
the expression that occurs before the AS keyword.
^The filename of the database follows the same semantics as the
filename argument to [sqlite3_open()] and [sqlite3_open_v2()]; the
special name "[:memory:]" results in an [in-memory database] and an
empty string results in a new temporary database.
^The filename argument can be a [URI filename] if URI filename processing
is enable on the database connection.  The default behavior is for
URI filenames to be disabled, however that might change in a future release
of SQLite, so application developers are advised to plan accordingly.

<p>The name that occurs after the AS keyword is the name of the database
used internally by SQLite.
^The schema-names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  ^The main and temp databases cannot be attached or
detached.</p>

<p> ^(Tables in an attached database can be referred to using the syntax 
<i>schema-name.table-name</i>.)^  ^If the name of the table is unique
across all attached databases and the main and temp databases, then the
<i>schema-name</i> prefix is not required.  ^If two or more tables in
different databases have the same name and the 
<i>schema-name</i> prefix is not used on a table reference, then the
table chosen is the one in the database that was least recently attached.</p>

<p>
^Transactions involving multiple attached databases are atomic,
assuming that the main database is not "[:memory:]" and the 
[journal_mode] is not [WAL].  ^(If the main
database is ":memory:" or if the journal_mode is WAL, then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a [COMMIT] where two or more database files are updated,
some of those files might get the changes where others
might not.)^
</p>

<p> ^There is a limit, set using [sqlite3_limit()] and 
[SQLITE_LIMIT_ATTACHED], to the number of databases that can be
simultaneously attached to a single database connection.</p>

<title>SQL Comment Syntax</title>
<tcl>
hd_keywords *comments comment
</tcl>

<fancy_format>

<tcl>
RecursiveBubbleDiagram comment-syntax
</tcl>

<p>^Comments are not SQL commands, but can occur within the text of
SQL queries passed to [sqlite3_prepare_v2()] and related interfaces.
^Comments are treated as whitespace by the parser.
^Comments can begin anywhere whitespace 
can be found, including inside expressions that span multiple lines.
</p>

<p>^SQL comments begin with two consecutive "-" characters (ASCII 0x2d)
and extend up to and including the next newline character (ASCII 0x0a)
or until the end of input, whichever comes first.</p>

<p>^C-style comments begin
with "/*" and extend up to and including the next "*/" character pair
or until the end of input, whichever comes first.  ^C-style comments
can span multiple lines. </p>

<p>^Comments can appear anywhere whitespace can occur,
including inside expressions and in the middle of other SQL statements.
^Comments do not nest.
</p>

<title>CREATE INDEX</title>
<tcl>
hd_keywords *createindex {CREATE INDEX}
</tcl>

<fancy_format>
<h1>Syntax</h1>
<tcl>
RecursiveBubbleDiagram create-index-stmt
</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of table column
names and/or expressions that are used for the index key.
If the optional WHERE clause is included, then the index is a "[partial index]".
</p>

<p>^If the optional IF NOT EXISTS clause is present and another index
with the same name already exists, then this command becomes a no-op.</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>

<p>^Indexes are removed with the [DROP INDEX] command.</p>

<tcl>hd_fragment uniqueidx {unique index}</tcl>
<h2>Unique Indexes</h2>

<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.</p>

<p>^For the purposes of unique indices, all NULL values
are considered 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).  The interpretation used
by SQLite is the same and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard, which
is that all NULL values are equal to one another.</p>

<tcl>hd_fragment indexexpr</tcl>
<h2>Indexes on Expressions</h2>

<p>^Expressions in an index may not reference other tables
and may not use subqueries nor functions whose result might
change (ex: [random()] or [sqlite_version()]).
^Expressions in an index may only refer to columns in the table
that is being indexed.
Indexes on expression will not work with versions of SQLite prior
to [version 3.9.0] ([dateof:3.9.0]).
See the [Indexes On Expressions] document for additional information
about using general expressions in CREATE INDEX statements.

<tcl>hd_fragment {descidx} {descending indices} {descending index} \
    {descending indexes} {DESC}</tcl>
<h2>Descending Indexes</h2>

<p>^Each column name or expression can be followed by one
of the "ASC" or "DESC" keywords to indicate sort order.
^The sort order may or may not be ignored depending
on the database file format, and in particular the [schema format number].
^The "legacy" schema format (1) ignores index
sort order.  ^The descending index schema format (4) takes index sort order
into account.  Only versions of SQLite 3.3.0 ([dateof:3.3.0])
and later are able to understand
the descending index format. For compatibility, version of SQLite between 3.3.0
and 3.7.9 use the legacy schema format by default.  The newer schema format is
used by default in version 3.7.10 ([dateof:3.7.10]) and later.
^The [legacy_file_format pragma] can be used to change set the specific
behavior for any version of SQLite.</p>

<h2>NULLS FIRST and NULLS LAST</h2>

<p>The NULLS FIRST and NULLS LAST predicates are not supported
for indexes.  For [sort order|sorting purposes], SQLite considers NULL values 
to be smaller than all other values.  Hence NULL values always appear at
the beginning of an ASC index and at the end of a DESC index.</p>

<tcl>hd_fragment collidx {COLLATE}</tcl>
<h2>Collations</h2>

<p>^The COLLATE clause optionally following each column name
or expression defines a
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<title>CREATE TABLE</title>
<tcl>
hd_keywords *createtable {CREATE TABLE}
</tcl>

<table_of_contents>
<h1>Syntax</h1>

<tcl>
RecursiveBubbleDiagram create-table-stmt
</tcl>

<h1>The CREATE TABLE command</h1>

<p>The "CREATE TABLE" command is used to create a new table in an SQLite 
database. A CREATE TABLE command specifies the following attributes of the
new table:

<ul>
  <li><p>The name of the new table.

  <li><p> The database in which the new table is created. Tables may be 
      created in the main database, the temp database, or in any attached
      database.

  <li><p> The name of each column in the table.

  <li><p> The declared type of each column in the table.

  <li><p> A default value or expression for each column in the table.

  <li><p> A default collation sequence to use with each column.

  <li><p> Optionally, a PRIMARY KEY for the table. Both single column and
       composite (multiple column) primary keys are supported.

  <li><p> A set of SQL constraints for each table. SQLite supports UNIQUE, NOT
       NULL, CHECK and FOREIGN KEY constraints.

  <li><p> Optionally, a [generated column] constraint.

  <li><p> Whether the table is a [WITHOUT ROWID] table.
</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with
  "sqlite_".)^

<p> ^If a <yyterm>schema-name</yyterm> is specified, it must be either "main", 
  "temp", or the name of an [ATTACH DATABASE|attached database]. ^In this case
  the new table is created in the named database. ^If the "TEMP" or "TEMPORARY"
  keyword occurs between the "CREATE" and "TABLE" then the new table is
  created in the temp database. ^It is an error to specify both a 
  <yyterm>schema-name</yyterm> and the TEMP or TEMPORARY keyword, unless the
  <yyterm>schema-name</yyterm> is "temp". 
  ^If no schema name is specified and the
  TEMP keyword is not present then the table is created in the main
  database.

<p>
  ^It is usually an error to attempt to create a new table in a database that
  already contains a table, index or view of the same name. ^However, if the
  "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and
  a table or view of the same name already exists, the CREATE TABLE command
  simply has no effect (and no error message is returned). ^An error is still
  returned if the table cannot be created because of an existing index, even 
  if the "IF NOT EXISTS" clause is specified.

<p>^It is not an error to create a table that has the same name as an 
  existing [CREATE TRIGGER|trigger].

<p>^Tables are removed using the [DROP TABLE] statement.  </p>

<tcl>hd_fragment createtabas {CREATE TABLE AS}</tcl>
<h2>CREATE TABLE ... AS SELECT Statements</h2>

<p>^(A "CREATE TABLE ... AS SELECT" statement creates and populates a database
table based on the results of a SELECT statement.)^ ^(The table has the same
number of columns as the rows returned by the SELECT statement. The name of
each column is the same as the name of the corresponding column in the result
set of the SELECT statement.)^ ^(The declared type of each column is determined
by the [expression affinity] of the corresponding expression in the result set
of the SELECT statement, as follows:
</p>

<center><table border=1>
  <tr><th>Expression Affinity   <th>Column Declared Type
  <tr><td>TEXT                  <td>"TEXT"
  <tr><td>NUMERIC               <td>"NUM"
  <tr><td>INTEGER               <td>"INT"
  <tr><td>REAL                  <td>"REAL"
  <tr><td>BLOB (a.k.a "NONE")   <td>"" (empty string)
</table></center>)^

<p>^(A table created using CREATE TABLE AS has no PRIMARY KEY and no
constraints of any kind. The default value of each column is NULL. The default
collation sequence for each column of the new table is BINARY.)^

<p>^Tables created using CREATE TABLE AS are initially populated with the
rows of data returned by the SELECT statement. ^Rows are assigned contiguously
ascending [rowid] values, starting with 1, in the [order by|order] that they
are returned by the SELECT statement.

<tcl>hd_fragment {tablecoldef} {column definition} {column definitions}</tcl>
<h1>Column Definitions</h1>

<p>Unless it is a CREATE TABLE ... AS SELECT statement, a CREATE TABLE includes
one or more [column-def|column definitions], optionally followed by a list of
[table-constraint|table constraints].  Each column definition consists of the
name of the column, optionally followed by the declared type of the column,
then one or more optional [column-constraint|column constraints]. Included in
the definition of "column constraints" for the purposes of the previous
statement are the COLLATE and DEFAULT clauses, even though these are not really
constraints in the sense that they do not restrict the data that the table may
contain. The other constraints - NOT NULL, CHECK, UNIQUE, PRIMARY KEY and
FOREIGN KEY constraints - impose restrictions on the table data.

<p>^The number of columns in a table is limited by the [SQLITE_MAX_COLUMN]
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>

<h2>Column Data Types</h2>

<p>^Unlike most SQL databases, SQLite does not restrict the type of data that
may be inserted into a column based on the columns declared type. Instead,
SQLite uses [dynamic typing]. ^The declared type of a column is used to
determine the [affinity] of the column only.

<tcl>hd_fragment dfltval {default column value} {default value} {DEFAULT clauses}</tcl>
<h2>The DEFAULT clause</h2>
<p>The DEFAULT clause specifies a default value to use for the column if no
value is explicitly provided by the user when doing an [INSERT]. ^If there
is no explicit DEFAULT clause attached to a column definition, then the 
default value of the column is NULL. ^(An explicit DEFAULT clause may specify
that the default value is NULL, a string constant, a blob constant, a
signed-number, or any constant expression enclosed in parentheses. A
default value may also be one of the special case-independent keywords
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.)^ ^For the purposes of the
DEFAULT clause, an expression is considered constant if it
contains no sub-queries, column or table references, [bound parameters],
or string literals enclosed in double-quotes instead of single-quotes.

<p>^(Each time a row is inserted into the table by an INSERT statement that 
does not provide explicit values for all table columns the values stored in
the new row are determined by their default values)^, as follows:

<ul>
  <li><p>^If the default value of the column is a constant NULL, text, blob or
    signed-number value, then that value is used directly in the new row.

  <li><p>^If the default value of a column is an expression in parentheses, then
    the expression is evaluated once for each row inserted and the results
    used in the new row.

  <li><p>^If the default value of a column is CURRENT_TIME, CURRENT_DATE or
    CURRENT_TIMESTAMP, then the value used in the new row is a text
    representation of the current UTC date and/or time. ^For CURRENT_TIME, the
    format of the value is "HH:MM:SS". ^For CURRENT_DATE, "YYYY-MM-DD". ^The
    format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
</ul>

<tcl>hd_fragment collateclause {COLLATE clause} {COLLATE constraint}</tcl>
<h2>The COLLATE clause</h2>
<p>^The COLLATE clause specifies the name of a [collating sequence] to use as
the default collation sequence for the column. ^If no COLLATE clause is
specified, the default collation sequence is [BINARY].

<h2>The GENERATED ALWAYS AS clause</h2>
<p>A column that includes a GENERATED ALWAY AS clause is a [generated column].
Generated columns are supported beginning with SQLite verison 3.31.0 ([dateof:3.31.0]).
See the [generated column|separate documentation] for details on the capabilities and
limitations of generated columns.

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<h2>The PRIMARY KEY</h2>
<p>^Each table in SQLite may have at most one PRIMARY KEY. ^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.
  ^The PRIMARY KEY clause must contain only column names &mdash; the use of 
  expressions in an [indexed-column] of a PRIMARY KEY is not supported.
  ^An error is raised if more than one PRIMARY KEY clause appears in a
  CREATE TABLE statement.  ^The PRIMARY KEY is optional for ordinary tables
  but is required for [WITHOUT ROWID] tables.

<p>If a table has a single column primary key and the declared type of that
  column is "INTEGER" and the table is not a [WITHOUT ROWID] table,
  then the column is known as an [INTEGER PRIMARY KEY].
  See [rowid|below] for a description of the special properties and behaviors
  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must have a unique combination
  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
  have identical primary key values, that is a constraint violation.

<p> 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} {UNIQUE constraint}</tcl>
<h2>UNIQUE constraints</h2>
<p>^A UNIQUE 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.
  ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain
  only column names &mdash; the use of 
  expressions in an [indexed-column] of a UNIQUE [table-constraint]
  is not supported.
  

<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 
  [WITHOUT ROWID] tables.)
  Hence, the following schemas are logically equivalent:

  <ol>
  <li><p>CREATE TABLE t1(a, b UNIQUE);
  <li><p>CREATE TABLE t1(a, b PRIMARY KEY);
  <li><p>CREATE TABLE t1(a, b);<br>
         CREATE UNIQUE INDEX t1b ON t1(b);
  </ol>

<tcl>hd_fragment {ckconst} {CHECK} {CHECK constraint} {CHECK constraints}</tcl>
<h2>CHECK constraints</h2>
<p>^(A CHECK constraint may be attached to a column definition or
  specified as a table constraint. In practice it makes no difference.)^ ^(Each
  time a new row is inserted into the table or an existing row is updated,
  the expression associated with each CHECK constraint is evaluated and
  cast to a NUMERIC value in the same way as a [CAST expression]. If the 
  result is zero (integer value 0 or real value 0.0), then a constraint
  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.

<tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}</tcl>
<h2>NOT NULL constraints</h2>
<p>^A NOT NULL 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.)^


<h1>Constraint violations</h1>
<p>Exactly how a constraint violation is dealt with is determined by the
  [conflict clause|constraint conflict resolution algorithm]. Each 
  PRIMARY KEY, UNIQUE, NOT NULL and CHECK constraint has a default conflict
  resolution algorithm. ^PRIMARY KEY, UNIQUE and NOT NULL constraints may be
  explicitly assigned a default conflict resolution algorithm by including
  a [conflict-clause] in their definitions. ^Or, if a constraint definition
  does not include a [conflict-clause] or it is a CHECK constraint, the default
  conflict resolution algorithm is ABORT. ^Different constraints within the
  same table may have different default conflict resolution algorithms. See
  the section titled [ON CONFLICT] for additional information.

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>
<h1>ROWIDs and the INTEGER PRIMARY KEY</h1>

<p>^Except for [WITHOUT ROWID] tables, all rows within SQLite tables
have a 64-bit signed integer key that uniquely identifies the row within its table.
This integer is usually
called the "rowid". ^The rowid value can be accessed using one of the special
case-independent names "rowid", "oid", or "_rowid_" in place of a column name.
^If a table contains a user defined column named "rowid", "oid" or "_rowid_",
then that name always refers the explicitly declared column and cannot be used
to retrieve the integer rowid value.

<p>The rowid (and "oid" and "_rowid_") is omitted in [WITHOUT ROWID] tables.
WITHOUT ROWID tables are only available in SQLite [version 3.8.2]
([dateof:3.8.2]) and later.
A table that lacks the WITHOUT ROWID clause is called a "rowid table".

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

<p> The exception mentioned above is that ^if the declaration of a column with
declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not
become an alias for the rowid and is not classified as an integer primary key.
This quirk is not by design. It is due to a bug in early versions of SQLite.
But fixing the bug could result in backwards incompatibilities.
Hence, the original behavior has been retained (and documented) because odd
behavior in a corner case is far better than a compatibility break.  This means
that ^(the following three table declarations all cause the column "x" to be an
alias for the rowid (an integer primary key):

<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));</tt>
</ul>)^

<p>But ^(the following declaration does not result in "x" being an alias for
the rowid:
<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);</tt>
</ul>)^

<p>^Rowid values may be modified using an UPDATE statement in the same
way as any other column value can, either using one of the built-in aliases
("rowid", "oid" or "_rowid_") or by using an alias created by an integer
primary key. ^Similarly, an INSERT statement may provide a value to use as the
rowid for each row inserted. ^(Unlike normal SQLite columns, an integer primary
key or rowid column must contain integer values. Integer primary key or rowid
columns are not able to hold floating point values, strings, BLOBs, or NULLs.)^

<p>^If an UPDATE statement attempts to set an integer primary key or rowid column
to a NULL or blob value, or to a string or real value that cannot be losslessly
converted to an integer, a "datatype mismatch" error occurs and the statement
is aborted. ^If an INSERT statement attempts to insert a blob value, or a string
or real value that cannot be losslessly converted to an integer into an
integer primary key or rowid column, a "datatype mismatch" error occurs and the
statement is aborted.

<p>^If an INSERT statement attempts to insert a NULL value into a rowid or
integer primary key column, the system chooses an integer value to use as the
rowid automatically. A detailed description of how this is done is provided
<a href="autoinc.html">separately</a>.</p>

<p>^(The [parent key] of a [foreign key constraint] is not allowed to
use the rowid.  The parent key must used named columns only.)^</p>

<title>Savepoints</title>
<tcl>
hd_keywords *savepoint SAVEPOINT RELEASE
</tcl>

<fancy_format>
<h1>Syntax</h1>

<tcl>
RecursiveBubbleDiagram savepoint-stmt
RecursiveBubbleDiagram release-stmt
RecursiveBubbleDiagram rollback-stmt
</tcl>

<h1>Savepoints</h1>

<p> ^SAVEPOINTs are a method of creating transactions, similar to
[BEGIN] and [COMMIT], except that the SAVEPOINT and RELEASE commands
are named and may be nested.</p>

<p> ^The SAVEPOINT command starts a new transaction with a name.
^The transaction names need not be unique.
^(A SAVEPOINT can be started either within or outside of
a [BEGIN]...[COMMIT].)^  ^(When a SAVEPOINT is the outer-most savepoint
and it is not within a [BEGIN]...[COMMIT] then the behavior is the
same as BEGIN DEFERRED TRANSACTION.)^</p>

<p>^The ROLLBACK TO command reverts the state of the database back to what
it was just after the corresponding SAVEPOINT.  ^Note that unlike that
plain [ROLLBACK] command (without the TO keyword) the ROLLBACK TO command
does not cancel the transaction.  ^Instead of cancelling the transaction,
the ROLLBACK TO command restarts the transaction again at the beginning.
^All intervening SAVEPOINTs are canceled, however.</p>

<p>^The RELEASE command is like a [COMMIT] for a SAVEPOINT.
^The RELEASE command causes all savepoints back to and including the 
most recent savepoint with a matching name to be removed from the 
transaction stack.  ^The RELEASE of an inner transaction
does not cause any changes to be written to the database file; it merely
removes savepoints from the transaction stack such that it is
no longer possible to ROLLBACK TO those savepoints.
^If a RELEASE command releases the outermost savepoint, so
that the transaction stack becomes empty, then RELEASE is the same
as [COMMIT].
^The [COMMIT] command may be used to release all savepoints and
commit the transaction even if the transaction was originally started
by a SAVEPOINT command instead of a [BEGIN] command.</p>

<p>^If the savepoint-name in a RELEASE command does not match any
savepoint currently in the transaction stack, then no savepoints are
released, the database is unchanged, and the RELEASE command returns
an error.</p>

<p>^Note that an inner transaction might commit (using the RELEASE command)
but then later have its work undone by a ROLLBACK in an outer transaction.
^A power failure or program crash or OS crash will cause the outer-most
transaction to rollback, undoing all changes that have occurred within
that outer transaction, even changes that have supposedly been "committed"
by the RELEASE command.  ^Content is not actually committed on the disk 
until the outermost transaction commits.</p>

<p>There are several ways of thinking about the RELEASE command:</p>

<ul>
<li><p>
Some people view RELEASE as the equivalent of COMMIT for a SAVEPOINT.
This is an acceptable point of view as long as one remembers that the
changes committed by an inner transaction might later be undone by a
rollback in an outer transaction.</p></li>

<li><p>
Another view of RELEASE is that it merges a named transaction into its
parent transaction, so that the named transaction and its parent become
the same transaction.  After RELEASE, the named transaction and its parent
will commit or rollback together, whatever their fate may be.
</p></li>

<li><p>
One can also think of savepoints as
"marks" in the transaction timeline.  In this view, the SAVEPOINT command
creates a new mark, the ROLLBACK TO command rewinds the timeline back
to a point just after the named mark, and the RELEASE command
erases marks from the timeline without actually making any
changes to the database.
</p></li>
</ul>



<h1>Transaction Nesting Rules</h1>

<p>^The last transaction started will be the first
transaction committed or rolled back.</p>

<p>^The [BEGIN] command only works if the transaction stack is empty, or
in other words if there are no pending transactions.  ^If the transaction
stack is not empty when the [BEGIN] command is invoked, then the command
fails with an error.</p>

<p>^The [COMMIT] command commits all outstanding transactions and leaves
the transaction stack empty.</p>

<p>^The RELEASE command starts with the most recent addition to the
transaction stack and releases savepoints backwards 
in time until it releases a savepoint with a matching savepoint-name.
^Prior savepoints, even savepoints with matching savepoint-names, are
unchanged.
^If the RELEASE command causes the
transaction stack to become empty (if the RELEASE command releases the
outermost transaction from the stack) then the transaction commits.</p>

<p>^The [ROLLBACK] command without a TO clause rolls backs all transactions
and leaves the transaction stack empty.</p>

<p>^The ROLLBACK command with a TO clause rolls back transactions going
backwards in time back to the most recent SAVEPOINT with a matching name.
^The SAVEPOINT with the matching name remains on the transaction stack,
but all database changes that occurred after that SAVEPOINT was created
are rolled back.  ^If the savepoint-name in a ROLLBACK TO command does not
match any SAVEPOINT on the stack, then the ROLLBACK command fails with an
error and leaves the state of the database unchanged.</p>

<title>Transaction</title>
<tcl>
hd_keywords transaction BEGIN COMMIT ROLLBACK
</tcl>

<fancy_format>
<h1>Transaction Control Syntax</h1>

<tcl>
RecursiveBubbleDiagram begin-stmt
RecursiveBubbleDiagram commit-stmt
RecursiveBubbleDiagram rollback-stmt
</tcl>

<h1>Transactions</h1>

<p>
^No reads or writes occur except within a transaction.
^Any command that accesses the database (basically, any SQL command,
except a few [PRAGMA] statements)
will automatically start a transaction if
one is not already in effect.  ^Automatically started transactions
are committed when the last SQL statement finishes.
</p>

<p>
^Transactions can be started manually using the BEGIN
command.  ^(Such transactions usually persist until the next
COMMIT or ROLLBACK command.  But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.)^
See the documentation on the [ON CONFLICT]
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>

<p>
^END TRANSACTION is an alias for COMMIT.
</p>

<p> ^(Transactions created using BEGIN...COMMIT do not nest.)^
^For nested transactions, use the [SAVEPOINT] and [RELEASE] commands.
The "TO SAVEPOINT <yyterm>name</yyterm>" clause of the ROLLBACK command shown
in the syntax diagram above is only applicable to [SAVEPOINT]
transactions.  ^An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by [SAVEPOINT] or a prior BEGIN.
^The COMMIT command and the ROLLBACK command without the TO clause
work the same on [SAVEPOINT] transactions as they do with transactions
started by BEGIN.</p>

<h2>Read transactions versus write transactions</h2>

<p>SQLite current supports multiple simultaneous read transactions
coming from separate database connections, possibly in separate
threads or processes, but only one simultaneous write transaction.
<p>

<p>A read transaction is used for reading only.  A write transaction
allows both reading and writing.  A read transaction is started
by a SELECT statement, and a write transaction is started by 
statements like CREATE, DELETE, DROP, INSERT, or UPDATE (collectively
"write statements").  If a write statement occurs while
a read transaction is active, then the read transaction is upgraded
to write transaction if possible.  If some other database connection
has already modified the database or is already in the process of
modifying the database, then upgrading to a write transaction is
not possible and the write statement will fail with [SQLITE_BUSY].
</p>

<p>
While a read transaction is active, any changes to the database that
are implemented by separate database connections will not be seen
by the database connection that started the read transaction.  If database
connection X is holding a read transaction, it is possible that some
other database connection Y might change the content of the database
while X's transaction is still open, however X will not be able to see 
those changes until after the transaction ends.  While its read
transaction is active, X will continue to see an historic snapshot
the database prior to the changes implemented by Y.
</p>


<tcl>hd_fragment immediate {BEGIN IMMEDIATE} {BEGIN EXCLUSIVE}</tcl>
<h2>DEFERRED, IMMEDIATE, and EXCLUSIVE transactions</h2>

<p>
^Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE.
^The default transaction behavior is DEFERRED.
</p>

<p>
^DEFERRED means that the transaction does not actually
start until the database is first accessed.  ^Internally,
the BEGIN DEFERRRED statement merely sets a flag on the database 
connection that turns off the automatic commit that would normally
occur when the last statement finishes.  This causes the transaction
that is automatically started to persist until an explicit
COMMIT or ROLLBACK or until a rollback is provoked by an error
or an ON CONFLICT ROLLBACK clause.  If the first statement after
BEGIN DEFERRED is a SELECT, then a read transaction is started.
Subsequent write statements will upgrade the transaction to a
write transaction if possible, or return SQLITE_BUSY.  If the
first statement after BEGIN DEFERRED is a write statement, then
a write transaction is started.
</p>

<p>
^IMMEDIATE cause the database connection to start a new write
immediately, without waiting for a writes statement.  The 
BEGIN IMMEDIATE might fail with [SQLITE_BUSY] if another write
transaction is already active on another database connection.
</p>

<p>
^EXCLUSIVE is similar to IMMEDIATE in that a write transaction
is started immediately.  EXCLUSIVE and IMMEDIATE are the same
in [WAL mode], but in other journaling modes, EXCLUSIVE prevents
other database connections from reading the database while the
transaction is underway.
</p>

<h2>Implicit versus explicit transactions</h2>

<p>
An implicit transaction (a transaction that is started automatically,
not a transaction started by BEGIN) is committed automatically when
the last active statement finishes.  A statement finishes when its
last cursor closes, which is guaranteed to happen when the
prepared statement is [sqlite3_reset() | reset] or
[sqlite3_finalize() | finalized].  Some statements might "finish"
for the purpose of transaction control prior to being reset or finalized,
but there is no guarantee of this.  The only way to ensure that a
statement has "finished" is to invoke [sqlite3_reset()] or
[sqlite3_finalize()] on that statement.  An open [sqlite3_blob] used for
incremental BLOB I/O also counts as an unfinished statement.
The [sqlite3_blob] finishes when it is [sqlite3_blob_close() | closed].
</p>

<p>
^The explicit COMMIT command runs immediately, even if there are
pending [SELECT] statements.  ^However, if there are pending
write operations, the COMMIT command
will fail with an error code [SQLITE_BUSY].
</p>

<p>
^An attempt to execute COMMIT might also result in an [SQLITE_BUSY] return code
if an another thread or process has an open read connection.
^When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
</p>

<p>
In very old versions of SQLite (before version 3.7.11 - [dateof:3.7.11])
the ROLLBACK will fail with an error code 
[SQLITE_BUSY] if there are any pending queries.  ^In more recent
versions of SQLite, the ROLLBACK will proceed and pending statements
will often be aborted, causing them to return an [SQLITE_ABORT] or
[SQLITE_ABORT_ROLLBACK] error.
^In SQLite version 3.8.8 ([dateof:3.8.8]) and later,
a pending read will continue functioning
after the ROLLBACK as long as the ROLLBACK does not modify the database
schema.
</p>

<p>
If [PRAGMA journal_mode] is set to OFF (thus disabling the rollback journal
file) then the behavior of the ROLLBACK command is undefined.
</p>

<h1>Response To Errors Within A Transaction</h1>

<p> ^(If certain kinds of errors occur within a transaction, the
transaction may or may not be rolled back automatically.  The
errors that can cause an automatic rollback include:</p>

<ul>
<li> [SQLITE_FULL]: database or disk full
<li> [SQLITE_IOERR]: disk I/O error
<li> [SQLITE_BUSY]: database in use by another process
<li> [SQLITE_NOMEM]: out or memory
</ul>)^

<p>
^For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.  ^However, 
depending on the statement being evaluated and the point at which the
error occurs, it might be necessary for SQLite to rollback and
cancel the entire transaction.  ^An application can tell which
course of action SQLite took by using the
[sqlite3_get_autocommit()] C-language interface.</p>

<p>It is recommended that applications respond to the errors
listed above by explicitly issuing a ROLLBACK command.  ^If the 
transaction has already been rolled back automatically
by the error response, then the ROLLBACK command will fail with an
error, but no harm is caused by this.</p>

<p>Future versions of SQLite may extend the list of errors which
might cause automatic transaction rollback.  Future versions of
SQLite might change the error response.  In particular, we may
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</p>