Documentation Source Text

</tcl>

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

<p> ^(The RENAME TO syntax is used to rename the table identified by 
<i>&#91;schema-name.&#93;table-name</i> to <i>new-table-name</i>.)^
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p> ^If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.  ^However, if there are
any view definitions, or statements executed by triggers that refer to
................................................................................

<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 <i>name</i>" 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>
................................................................................
</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 &lt;schema-name&gt; 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 
  &lt;schema-name&gt; and the TEMP or TEMPORARY keyword, unless the

  &lt;schema-name&gt; 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
................................................................................
described under [constraints|SQL Data Constraints] below.

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


<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
................................................................................
<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.
  ^The PRIMARY KEY clause must contain only column names &mdash; the use of 
  expressions in an &lt;indexed-column&gt; 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 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
................................................................................
  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 &lt;indexed-column&gt; 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.)
................................................................................
</p>

<p>^If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "VIEW" then the view that is created is only visible to the
[database connection] that created it and is automatically deleted when
the database connection is closed.</p>

<p> ^If a &lt;schema-name&gt; is specified, then the view is created in 

the specified database. ^It is an error to specify both a &lt;schema-name&gt;
and the TEMP keyword on a VIEW, unless the &lt;schema-name&gt; is "temp".

^If no schema name is specified, and the TEMP keyword is not present,
the VIEW is created in the main database.</p>

<p>^You cannot [DELETE], [INSERT], or [UPDATE] a view.  ^Views are read-only 
in SQLite.  ^However, in many cases you can use an
[INSTEAD OF trigger] on the view to accomplish 
the same thing.  ^Views are removed 
with the [DROP VIEW] command.</p>

<p>^If a list of column names follows the &lt;view-name&gt;, then those

becomes the names of the columns for the view.  ^If no list of column
names is provided, then the names of the columns in the view are derived
from the names of the result-set columns in the &lt;select-stmt&gt;.

<p>Note that the list of column names following the view-name is only
supported in SQLite versions 3.8.12 and later.  Views with specified column
names will not work in earlier versions of SQLite.

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL TABLE}}

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>
................................................................................
in the database file.</p>

<p>In general, you can do anything with a [virtual table] that can be done
with an ordinary table, except that ^you cannot create indices or triggers on a
virtual table.  ^Some virtual table implementations might impose additional
restrictions.  For example, many virtual tables are read-only.</p>

<p>The &lt;module-name&gt; is the name of an object that implements
the virtual table.  ^The &lt;module-name&gt; must be registered with
the SQLite database connection using
[sqlite3_create_module()] or [sqlite3_create_module_v2()]
prior to issuing the CREATE VIRTUAL TABLE statement.
^The module takes zero or more comma-separated arguments.
^The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as [column definitions] in a traditional
................................................................................
##############################################################################
Section DELETE delete {DELETE *DELETEs}

RecursiveBubbleDiagram delete-stmt
</tcl>

<p>The DELETE command removes records from the table identified by the
   <i>qualified-table-name</i>. 

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^If a WHERE clause is supplied, then only those rows for which the
   result of evaluating the WHERE clause as a [boolean expression|
   boolean expression is true] are deleted.

<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>

<p>The following restrictions apply to DELETE statements that occur within the
   body of a [CREATE TRIGGER] statement:

<ul>

  <li><p>^The <i>table-name</i> specified as part of a DELETE statement within
    a trigger body must be unqualified.  ^(In other words, the
    <i>schema-name</i><b>.</b> prefix on the table name is not allowed 
    within triggers.)^ ^If the table to which the trigger is attached is
    not in the temp database, then DELETE statements within the trigger
    body must operate on tables within the same database as it. ^If the table
    to which the trigger is attached is in the TEMP database, then the
    unqualified name of the table being deleted is resolved in the same way as
................................................................................
^All indices and triggers
associated with the table are also deleted.</p>

<p>^The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an
implicit [DELETE | DELETE FROM &lt;tbl&gt;] command before removing the
table from the database schema. ^Any triggers attached to the table are
dropped from the database schema before the implicit DELETE FROM &lt;tbl&gt; 
is executed, so this cannot cause any triggers to fire. By contrast, ^an
implicit DELETE FROM &lt;tbl&gt; does cause any configured
[foreign key actions] to take place. 
^If the implicit DELETE FROM &lt;tbl&gt; executed
as part of a DROP TABLE command violates any immediate foreign key constraints,
an error is returned and the table is not dropped. ^If 
the implicit DELETE FROM &lt;tbl&gt; causes any 
deferred foreign key constraints to be violated, and the violations still
exist when the transaction is committed, an error is returned at the time
of commit.

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}
................................................................................
a correlated subquery. ^A correlated subquery is reevaluated each time
its result is required. ^An uncorrelated subquery is evaluated only once
and the result reused as necessary.

<tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl>
<h3>CAST expressions</h3>

<p>A CAST expression of the form "CAST(&lt;expr&gt; AS &lt;type-name&gt;)"
is used to convert the value of &lt;expr&gt; to 
a different [storage class] specified by &lt;type-name&gt;.
^A CAST conversion is similar to the conversion that takes
place when a [column affinity] is applied to a value except that with
the CAST operator the conversion always takes place even if the conversion
lossy and irreversible, whereas column affinity only changes the data type
of a value if the change is lossless and reversible.

<p>^If the value of &lt;expr&gt; is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result
is determined by applying the [rules for determining column affinity] to
the &lt;type-name&gt;.

<table border=1>
<tr>
  <th> Affinity of &lt;type-name&gt;
  <th> Conversion Processing
<tr>
  <td> NONE 

  <td> ^Casting a value to a &lt;type-name&gt; with no affinity causes the value to
  be converted into a BLOB.  ^Casting to a BLOB consists of first casting
  the value to TEXT in the [encoding] of the database connection, then
  interpreting the resulting byte sequence as a BLOB instead of as TEXT.

<tr>
  <td> TEXT
  <td> ^To cast a BLOB value to TEXT, the sequence of bytes that make up the
................................................................................
  <td> ^When casting a BLOB value to INTEGER, the value is first converted to
        TEXT.
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.  ^(The CAST operator understands decimal integers
        only &mdash; conversion of [hexadecimal integers] stops at the "x" in the
        "0x" prefix of the hexadecimal integer string and thus result of the 
        CAST is always zero.)^

      <p>^A cast of a REAL value into an INTEGER results in the integer
      between the REAL value and zero that is closest to the REAL value.
      ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed
      integer (-9223372036854775808) then the result is the least possible
................................................................................
RecursiveBubbleDiagram insert-stmt
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. ^If no column-list is specified then the number

of values inserted into each row
must be the same as the number of columns in the table. ^In this case
the result of evaluating the left-most expression from each term of
the VALUES list is inserted into the left-most column of each new row,
and so forth for each subsequent expression. ^If a
column-list is specified, then the number of values in each term of the
VALUE list must match the number of
specified columns. ^Each of the named columns of the new row is populated
with the results of evaluating the corresponding VALUES expression. ^Table
columns that do not appear in the column list are populated with the default
column value (specified as part of the CREATE TABLE statement), or with NULL if
no default value is specified.

<li><p>The second form of the INSERT statement contains a SELECT statement
instead of a VALUES clause. ^A new entry is inserted into the table for each
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.

<li><p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its default value,
or with a NULL if no default value is specified as part of the column
definition in the CREATE TABLE statement.

</ul>

<p>^The "REPLACE" and "INSERT OR <i>action</i>" forms specify an alternative
constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled [ON CONFLICT] for additional information.
For compatibility with MySQL, ^the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".

<p>^(The optional "<i>schema-name</i><b>.</b>" prefix on the <i>table-name</i>

is support for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>

................................................................................
all indices in all attached databases that use the named collation sequences
are recreated. 

<p>^Or, if the argument attached to the REINDEX identifies a specific 
database table, then all indices attached to the database table are rebuilt. 
^If it identifies a specific database index, then just that index is recreated.


<p>^If no <i>schema-name</i> is specified and there exists both a table or
index and a collation sequence of the specified name, SQLite interprets
this as a request to rebuild the indices that use the named collation sequence.
This ambiguity in the syntax may be avoided by always specifying a
<i>schema-name</i> when reindexing a specific table or index.

<tcl>
###############################################################################
Section REPLACE replace REPLACE

</tcl>

................................................................................
</ol>

<p>To put it another way, a recursive common table expression must
look like the following:

<tcl>RecursiveBubbleDiagram recursive-cte</tcl>

<p>We refer to the table named by the cte-table-name in a recursive
common table expression as the "recursive table".
In the recursive-cte bubble diagram above, the recursive
table must appear exactly once in the FROM clause of the recursive-select

and must not appear anywhere else in either the initial-select or the

recursive-select, including subqueries.  The initial-select may be

a [compound select], but it may not include an ORDER BY, LIMIT, or OFFSET.
The recursive-select must be a simple select, not a compound.  The
recursive-select is allowed to include an ORDER BY, LIMIT, and/or OFFSET.

<p>The basic algorithm for computing the content of the recursive table
is as follows:

<ol>
<li> Run the initial-select and add the results to a queue.
<li> While the queue is not empty:
<ol type="a">
<li> Extract a single row from the queue.
<li> Insert that single row into the recursive table
<li> Pretend that the single row just extracted is the only
     row in the recursive table and run the recursive-select,
     adding all results to the queue.
................................................................................
</ol>
</ol>

<p>The basic procedure above may modified by the following additional rules:

<ul>
<li><p>
  If a UNION operator connects the initial-select with the
  recursive-select, then only add rows to the queue if no identical row has

  been previously added to the queue.  Repeated rows are discarded before being
  added to the queue even if the repeated rows have already been extracted
  from the queue by the recursion step.  If the operator is UNION ALL,
  then all rows generated by both the initial-select and the
  recursive-select are always added to the queue even if they are repeats.

  When determining if a row is repeated, NULL values compare
  equal to one another and not equal to any other value.
<li><p>
  The LIMIT clause, if present, determines the maximum number of rows that
  will ever be added to the recursive table in step 2b.
  Once the limit is reached, the recursion stops.
  A limit of zero means that no rows are ever added to the
  recursive table, and a negative limit means an unlimited number of rows
  may be added to the recursive table.
<li><p>
  The OFFSET clause, if it is present and has a positive value N, prevents the
  first N rows from being added to the recursive table.
  The first N rows are still processed by the recursive-select; they

  just are not added to the recursive table.  Rows are not counted toward
  fulfilling the LIMIT until all OFFSET rows have been skipped.
<li><p>
  If an ORDER BY clause is present, it determines the order in which rows
  are extracted from the queue in step 2a.  If there is no ORDER BY clause,
  then the order in which rows are extracted is undefined.  (In the current
  implementation, the queue becomes a FIFO if the ORDER BY clause is omitted,
................................................................................
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
<i>qualified-table-name</i> specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
table are modified by the UPDATE. ^Otherwise, the UPDATE affects only those
rows for which the result of evaluating the WHERE clause expression as a 
[boolean expression|boolean expression is true]. ^It is not an error if the
WHERE clause does not evaluate to true for any row in the table - this just
means that the UPDATE statement affects zero rows.

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each

assignment specifies a column name to the left of the equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of
assignment expressions, all but the rightmost occurrence is ignored. ^Columns
that do not appear in the list of assignments are left unmodified. ^The scalar
expressions may refer to columns of the row being updated. ^In this case all
scalar expressions are evaluated before any assignments are made.


<p>^The optional conflict-clause allows the user to nominate a specific
constraint conflict resolution algorithm to use during this one UPDATE command.
Refer to the section entitled [ON CONFLICT] for additional information.

<h3>Restrictions on UPDATE Statements Within CREATE TRIGGER</h3>

<p>The following additional syntax restrictions apply to UPDATE statements that
occur within the body of a [CREATE TRIGGER] statement.  

<ul>
  <li><p>^The <i>table-name</i> specified as part of an UPDATE statement within

      a trigger body must be unqualified. ^(In other words, the
      <i>schema-name</i><b>.</b> prefix on the table name of the UPDATE is
      not allowed within triggers.)^ ^Unless the table to which the trigger
      is attached is in the TEMP database, the table being updated by the
      trigger program must reside in the same database as it. ^If the table
      to which the trigger is attached is in the TEMP database, then the
      unqualified name of the table being updated is resolved in the same way
................................................................................
The INDEXED BY phrase is an SQLite extension and
is not portable to other SQL database engines.</p>

<tcl>
RecursiveBubbleDiagram qualified-table-name
</tcl>


<p>^The "INDEXED BY index-name" phrase specifies that the named index
must be used in order to look up values on the preceding table.
^If index-name does not exist or cannot be used for the query, then
the preparation of the SQL statement fails.
^(The "NOT INDEXED" clause specifies that no index shall be used when
accessing the preceding table, including implied indices create by
UNIQUE and PRIMARY KEY constraints.  However, the [rowid]
can still be used to look up entries even when "NOT INDEXED" is specified.)^</p>

<p>Some SQL database engines provide non-standard "hint" mechanisms which
can be used to give the query optimizer clues about what indices it should
................................................................................

<p>The INDEXED BY clause is <em>not</em> intended for use in tuning
the performance of a query.  The intent of the INDEXED BY clause is
to raise a run-time error if a schema change, such as dropping or
creating an index, causes the query plan for a time-sensitive query
to change.  The INDEXED BY clause is designed to help detect
undesirable query plan changes during regression testing.

Developers are admonished to omit all use of INDEXED BY during
application design, implementation, testing, and tuning.  If
INDEXED BY is to be used at all, it should be inserted at the very
end of the development process when "locking down" a design.</p>

<h3>See Also:</h3>

<ol>

</tcl>

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

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

<p> ^If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.  ^However, if there are
any view definitions, or statements executed by triggers that refer to
................................................................................

<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>
................................................................................
</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
................................................................................
described under [constraints|SQL Data Constraints] below.

<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}</tcl>
<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
................................................................................
<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.
  ^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
................................................................................
  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.)
................................................................................
</p>

<p>^If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "VIEW" then the view that is created is only visible to the
[database connection] that created it and is automatically deleted when
the database connection is closed.</p>

<p> ^If a <yyterm>schema-name</yyterm> is specified, then the view 
is created in the specified database.
^It is an error to specify both a <yyterm>schema-name</yyterm>
and the TEMP keyword on a VIEW, unless the <yyterm>schema-name</yyterm> 
is "temp".
^If no schema name is specified, and the TEMP keyword is not present,
the VIEW is created in the main database.</p>

<p>^You cannot [DELETE], [INSERT], or [UPDATE] a view.  ^Views are read-only 
in SQLite.  ^However, in many cases you can use an
[INSTEAD OF trigger] on the view to accomplish 
the same thing.  ^Views are removed 
with the [DROP VIEW] command.</p>

<p>^If a <yyterm>column-name</yyterm> list follows 
the <yyterm>view-name</yyterm>, then that list determines
the names of the columns for the view.  ^If the <yyterm>column-name</yyterm>
list is omitted, then the names of the columns in the view are derived
from the names of the result-set columns in the [select-stmt].

Note that the <yyterm>column-name</yyterm> list syntax is only
supported in SQLite versions 3.8.12 and later.


<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL TABLE}}

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>
................................................................................
in the database file.</p>

<p>In general, you can do anything with a [virtual table] that can be done
with an ordinary table, except that ^you cannot create indices or triggers on a
virtual table.  ^Some virtual table implementations might impose additional
restrictions.  For example, many virtual tables are read-only.</p>

<p>The <yyterm>module-name</yyterm> is the name of an object that implements
the virtual table.  ^The <yyterm>module-name</yyterm> must be registered with
the SQLite database connection using
[sqlite3_create_module()] or [sqlite3_create_module_v2()]
prior to issuing the CREATE VIRTUAL TABLE statement.
^The module takes zero or more comma-separated arguments.
^The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as [column definitions] in a traditional
................................................................................
##############################################################################
Section DELETE delete {DELETE *DELETEs}

RecursiveBubbleDiagram delete-stmt
</tcl>

<p>The DELETE command removes records from the table identified by the
   [qualified-table-name].

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^If a WHERE clause is supplied, then only those rows for which the
   WHERE clause [boolean expression] is true are deleted.
   ^Rows for which the expression is false or NULL are retained.

<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>

<p>The following restrictions apply to DELETE statements that occur within the
   body of a [CREATE TRIGGER] statement:

<ul>
  <li><p>^The <yyterm>table-name</yyterm> specified as part of a 
    DELETE statement within
    a trigger body must be unqualified.  ^(In other words, the
    <i>schema-name</i><b>.</b> prefix on the table name is not allowed 
    within triggers.)^ ^If the table to which the trigger is attached is
    not in the temp database, then DELETE statements within the trigger
    body must operate on tables within the same database as it. ^If the table
    to which the trigger is attached is in the TEMP database, then the
    unqualified name of the table being deleted is resolved in the same way as
................................................................................
^All indices and triggers
associated with the table are also deleted.</p>

<p>^The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an
implicit [DELETE | DELETE FROM] command before removing the
table from the database schema. ^Any triggers attached to the table are
dropped from the database schema before the implicit DELETE FROM
is executed, so this cannot cause any triggers to fire. By contrast, ^an
implicit DELETE FROM does cause any configured
[foreign key actions] to take place. 
^If the implicit DELETE FROM executed
as part of a DROP TABLE command violates any immediate foreign key constraints,
an error is returned and the table is not dropped. ^If 
the implicit DELETE FROM causes any 
deferred foreign key constraints to be violated, and the violations still
exist when the transaction is committed, an error is returned at the time
of commit.

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}
................................................................................
a correlated subquery. ^A correlated subquery is reevaluated each time
its result is required. ^An uncorrelated subquery is evaluated only once
and the result reused as necessary.

<tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl>
<h3>CAST expressions</h3>

<p>A CAST expression of the form "CAST(<i>expr</i> AS <i>type-name</i>)"
is used to convert the value of <i>expr</i> to 
a different [storage class] specified by <yyterm>type-name</yyterm>.
^A CAST conversion is similar to the conversion that takes
place when a [column affinity] is applied to a value except that with
the CAST operator the conversion always takes place even if the conversion
lossy and irreversible, whereas column affinity only changes the data type
of a value if the change is lossless and reversible.

<p>^If the value of <i>expr</i> is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result
is determined by applying the [rules for determining column affinity] to
the <yyterm>type-name</yyterm>.

<table border=1>
<tr>
  <th> Affinity of <yyterm>type-name</yyterm>
  <th> Conversion Processing
<tr>
  <td> NONE 
  <td> ^Casting a value to a <yyterm>type-name</yyterm> with no affinity 
  causes the value to
  be converted into a BLOB.  ^Casting to a BLOB consists of first casting
  the value to TEXT in the [encoding] of the database connection, then
  interpreting the resulting byte sequence as a BLOB instead of as TEXT.

<tr>
  <td> TEXT
  <td> ^To cast a BLOB value to TEXT, the sequence of bytes that make up the
................................................................................
  <td> ^When casting a BLOB value to INTEGER, the value is first converted to
        TEXT.
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.  ^(The CAST operator understands decimal
        integers only &mdash; conversion of [hexadecimal integers] stops 
        at the "x" in the "0x" prefix of the hexadecimal integer string 
        and thus result of the CAST is always zero.)^

      <p>^A cast of a REAL value into an INTEGER results in the integer
      between the REAL value and zero that is closest to the REAL value.
      ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed
      integer (-9223372036854775808) then the result is the least possible
................................................................................
RecursiveBubbleDiagram insert-stmt
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. ^If the <yyterm>column-name</yyterm> list after
<yyterm>table-name</yyterm> is omitted then the number
of values inserted into each row
must be the same as the number of columns in the table. ^In this case
the result of evaluating the left-most expression from each term of
the VALUES list is inserted into the left-most column of each new row,
and so forth for each subsequent expression. ^If a <yyterm>column-name</yyterm>
list is specified, then the number of values in each term of the
VALUE list must match the number of
specified columns. ^Each of the named columns of the new row is populated
with the results of evaluating the corresponding VALUES expression. ^Table
columns that do not appear in the column list are populated with the 
[default column value] (specified as part of the [CREATE TABLE] statement), or
with NULL if no [default value] is specified.

<li><p>The second form of the INSERT statement contains a [SELECT] statement
instead of a VALUES clause. ^A new entry is inserted into the table for each
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.

<li><p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its 
[default value], or with a NULL if no default value is specified 
as part of the column definition in the [CREATE TABLE] statement.

</ul>

<p>^The "REPLACE" and "INSERT OR <i>action</i>" forms specify an alternative
constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled [ON CONFLICT] for additional information.
For compatibility with MySQL, ^the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".

<p>^(The optional "<i>schema-name</i><b>.</b>" prefix on the 
<yyterm>table-name</yyterm>
is support for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>

................................................................................
all indices in all attached databases that use the named collation sequences
are recreated. 

<p>^Or, if the argument attached to the REINDEX identifies a specific 
database table, then all indices attached to the database table are rebuilt. 
^If it identifies a specific database index, then just that index is recreated.

<p>^If no <yyterm>schema-name</yyterm> is specified and 
there exists both a table or
index and a collation sequence of the specified name, SQLite interprets
this as a request to rebuild the indices that use the named collation sequence.
This ambiguity in the syntax may be avoided by always specifying a
<yyterm>schema-name</yyterm> when reindexing a specific table or index.

<tcl>
###############################################################################
Section REPLACE replace REPLACE

</tcl>

................................................................................
</ol>

<p>To put it another way, a recursive common table expression must
look like the following:

<tcl>RecursiveBubbleDiagram recursive-cte</tcl>

<p>Call the table named by the [cte-table-name] in a recursive
common table expression the "recursive table".
In the [recursive-cte] bubble diagram above, the recursive
table must appear exactly once in the FROM clause of the 
<yyterm>recursive-select</yyterm>
and must not appear anywhere else in either the
<yyterm>initial-select</yyterm> or the
<yyterm>recursive-select</yyterm>, including subqueries.
The <yyterm>initial-select</yyterm> may be
a [compound select], but it may not include an ORDER BY, LIMIT, or OFFSET.
The recursive-select must be a simple select, not a compound.  The
recursive-select is allowed to include an ORDER BY, LIMIT, and/or OFFSET.

<p>The basic algorithm for computing the content of the recursive table
is as follows:

<ol>
<li> Run the <yyterm>initial-select</yyterm> and add the results to a queue.
<li> While the queue is not empty:
<ol type="a">
<li> Extract a single row from the queue.
<li> Insert that single row into the recursive table
<li> Pretend that the single row just extracted is the only
     row in the recursive table and run the recursive-select,
     adding all results to the queue.
................................................................................
</ol>
</ol>

<p>The basic procedure above may modified by the following additional rules:

<ul>
<li><p>
  If a UNION operator connects the <yyterm>initial-select</yyterm> with the
  <yyterm>recursive-select</yyterm>, then only add rows to the queue if 
  no identical row has
  been previously added to the queue.  Repeated rows are discarded before being
  added to the queue even if the repeated rows have already been extracted
  from the queue by the recursion step.  If the operator is UNION ALL,
  then all rows generated by both the <yyterm>initial-select</yyterm> and the
  <yyterm>recursive-select</yyterm> are always added to the queue even if
  they are repeats.
  When determining if a row is repeated, NULL values compare
  equal to one another and not equal to any other value.
<li><p>
  The LIMIT clause, if present, determines the maximum number of rows that
  will ever be added to the recursive table in step 2b.
  Once the limit is reached, the recursion stops.
  A limit of zero means that no rows are ever added to the
  recursive table, and a negative limit means an unlimited number of rows
  may be added to the recursive table.
<li><p>
  The OFFSET clause, if it is present and has a positive value N, prevents the
  first N rows from being added to the recursive table.
  The first N rows are still processed
  by the <yyterm>recursive-select</yyterm> &mdash; they
  just are not added to the recursive table.  Rows are not counted toward
  fulfilling the LIMIT until all OFFSET rows have been skipped.
<li><p>
  If an ORDER BY clause is present, it determines the order in which rows
  are extracted from the queue in step 2a.  If there is no ORDER BY clause,
  then the order in which rows are extracted is undefined.  (In the current
  implementation, the queue becomes a FIFO if the ORDER BY clause is omitted,
................................................................................
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
[qualified-table-name] specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
table are modified by the UPDATE. ^Otherwise, the UPDATE affects only those
rows for which the WHERE clause
[boolean expression|boolean expression is true]. ^It is not an error if the
WHERE clause does not evaluate to true for any row in the table - this just
means that the UPDATE statement affects zero rows.

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each
assignment specifies a <yyterm>column-name</yyterm> to the left of the 
equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of
assignment expressions, all but the rightmost occurrence is ignored. ^Columns
that do not appear in the list of assignments are left unmodified. ^The scalar
expressions may refer to columns of the row being updated. ^In this case all
scalar expressions are evaluated before any assignments are made.

<p>^The optional "OR <i>action</i>" conflict clause that follows the
UPDATE keyword allows the user to nominate a specific
constraint conflict resolution algorithm to use during this one UPDATE command.
Refer to the section entitled [ON CONFLICT] for additional information.

<h3>Restrictions on UPDATE Statements Within CREATE TRIGGER</h3>

<p>The following additional syntax restrictions apply to UPDATE statements that
occur within the body of a [CREATE TRIGGER] statement.  

<ul>
  <li><p>^The <yyterm>table-name</yyterm> specified as part of an UPDATE 
      statement within
      a trigger body must be unqualified. ^(In other words, the
      <i>schema-name</i><b>.</b> prefix on the table name of the UPDATE is
      not allowed within triggers.)^ ^Unless the table to which the trigger
      is attached is in the TEMP database, the table being updated by the
      trigger program must reside in the same database as it. ^If the table
      to which the trigger is attached is in the TEMP database, then the
      unqualified name of the table being updated is resolved in the same way
................................................................................
The INDEXED BY phrase is an SQLite extension and
is not portable to other SQL database engines.</p>

<tcl>
RecursiveBubbleDiagram qualified-table-name
</tcl>

<p>^The "INDEXED BY <yyterm>index-name</yyterm>" phrase specifies 
that the named index
must be used in order to look up values on the preceding table.
^If <yyterm>index-name</yyterm> does not exist or cannot be used 
for the query, then the preparation of the SQL statement fails.
^(The "NOT INDEXED" clause specifies that no index shall be used when
accessing the preceding table, including implied indices create by
UNIQUE and PRIMARY KEY constraints.  However, the [rowid]
can still be used to look up entries even when "NOT INDEXED" is specified.)^</p>

<p>Some SQL database engines provide non-standard "hint" mechanisms which
can be used to give the query optimizer clues about what indices it should
................................................................................

<p>The INDEXED BY clause is <em>not</em> intended for use in tuning
the performance of a query.  The intent of the INDEXED BY clause is
to raise a run-time error if a schema change, such as dropping or
creating an index, causes the query plan for a time-sensitive query
to change.  The INDEXED BY clause is designed to help detect
undesirable query plan changes during regression testing.
Application 
developers are admonished to omit all use of INDEXED BY during
application design, implementation, testing, and tuning.  If
INDEXED BY is to be used at all, it should be inserted at the very
end of the development process when "locking down" a design.</p>

<h3>See Also:</h3>

<ol>

}
unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys

# Each pragma is recorded by invoking this procedure.
proc Pragma {namelist content} {
  global PragmaBody PragmaRef PragmaKeys
  set main_name [lindex $namelist 0]
  regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>database.</i><b>} content
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {
................................................................................
(Example:  <tt>'yes' &#91;FALSE&#93;</tt>.) Some pragmas
takes a string literal as their argument.  When pragma takes a keyword
argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes".
When querying the value of a setting, many pragmas return the number
rather than the keyword.</p>

<p>^A pragma may have an optional database name before the pragma name.

^The database name is the name of an [ATTACH]-ed database or it can be
"main" or "temp" for the main and the TEMP databases.  ^If the optional
database name is omitted, "main" is assumed.  ^In some pragmas, the database
name is meaningless and is simply ignored.  In the documentation below,
pragmas for which the database name is meaningful are shown with a
"<i>database.</i>" prefix.</p>



<tcl>Pragma {application_id} {
    <p><b>PRAGMA DB.application_id;
     <br>PRAGMA DB.application_id = </b><i>integer </i><b>;</b>
  

}
unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys

# Each pragma is recorded by invoking this procedure.
proc Pragma {namelist content} {
  global PragmaBody PragmaRef PragmaKeys
  set main_name [lindex $namelist 0]
  regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>schema.</i><b>} content
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {
................................................................................
(Example:  <tt>'yes' &#91;FALSE&#93;</tt>.) Some pragmas
takes a string literal as their argument.  When pragma takes a keyword
argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes".
When querying the value of a setting, many pragmas return the number
rather than the keyword.</p>

<p>^A pragma may have an optional <yyterm>schema-name</yyterm>
before the pragma name.
^The <yyterm>schema-name</yyterm> is the name of an [ATTACH]-ed database 
or "main" or "temp" for the main and the TEMP databases.  ^If the optional
schema name is omitted, "main" is assumed.  ^In some pragmas, the schema
name is meaningless and is simply ignored.  In the documentation below,
pragmas for which the schema name is meaningful are shown with a
"<i>schema.</i>" prefix.</p>



<tcl>Pragma {application_id} {
    <p><b>PRAGMA DB.application_id;
     <br>PRAGMA DB.application_id = </b><i>integer </i><b>;</b>
  

# appropriate <a href=""> markup.
#
# Links to keywords within the same main file are resolved using
# $::llink() if possible.  All other links and links that could
# not be resolved using $::llink() are resolved using $::glink().
# 
proc hd_resolve_2ndpass {text} {


  regsub -all {\[(.*?)\]} $text \
      "\175; hd_resolve_one \173\\1\175; hd_puts \173" text
  eval "hd_puts \173$text\175"
}
proc hd_resolve_one {x} {
  if {[string is integer $x]} {
    hd_puts \[$x\]
................................................................................
    .fancy p.todo        { border: solid #AA3333 1px; padding: 1ex }
    .fancy img { display:block; }
    .fancy :link:hover, .fancy :visited:hover { background: wheat }
    .fancy p,.fancy ul,.fancy ol,.fancy dl { margin: 1em 5ex }
    .fancy li p { margin: 1em 0 }
    /* End of "fancyformat" specific rules. */








    </style>
  }
  puts $fd {</head>}
  if {[file exists DRAFT]} {
    set tagline {<font size="6" color="red">*** DRAFT ***</font>}
  } else {
    set tagline {Small. Fast. Reliable.<br>Choose any three.}

# appropriate <a href=""> markup.
#
# Links to keywords within the same main file are resolved using
# $::llink() if possible.  All other links and links that could
# not be resolved using $::llink() are resolved using $::glink().
# 
proc hd_resolve_2ndpass {text} {
  regsub -all {<yyterm>} $text {<span class='yyterm'>} text
  regsub -all {</yyterm>} $text {</span>} text
  regsub -all {\[(.*?)\]} $text \
      "\175; hd_resolve_one \173\\1\175; hd_puts \173" text
  eval "hd_puts \173$text\175"
}
proc hd_resolve_one {x} {
  if {[string is integer $x]} {
    hd_puts \[$x\]
................................................................................
    .fancy p.todo        { border: solid #AA3333 1px; padding: 1ex }
    .fancy img { display:block; }
    .fancy :link:hover, .fancy :visited:hover { background: wheat }
    .fancy p,.fancy ul,.fancy ol,.fancy dl { margin: 1em 5ex }
    .fancy li p { margin: 1em 0 }
    /* End of "fancyformat" specific rules. */

    .yyterm {
      background: #fff;
      border: 1px solid #000;
      border-radius: 11px;
      padding-left: 4px;
      padding-right: 4px;
    }
    </style>
  }
  puts $fd {</head>}
  if {[file exists DRAFT]} {
    set tagline {<font size="6" color="red">*** DRAFT ***</font>}
  } else {
    set tagline {Small. Fast. Reliable.<br>Choose any three.}