Documentation Source Text

BubbleDiagram column-def
BubbleDiagram type-name
BubbleDiagram column-constraint
BubbleDiagram table-constraint
BubbleDiagram foreign-key-clause
</tcl>

<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.
</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;database-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;database-name&gt; and the TEMP or TEMPORARY keyword, unless the
  &lt;database-name&gt; is "temp". ^If no database 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>

<h3>CREATE TABLE AS Statements</h3>

<p>^A "CREATE TABLE AS" 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>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>
<h3>Column Definitions</h3>

<p>Unless it is a CREATE TABLE AS 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 tables data, and are are
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 an arbitrary expression enclosed in parentheses. An explicit
default value may also be one of the special case-independent keywords
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.)^

<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_DATETIME, 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>

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

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

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

<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE
  statement, it is an error.

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

<p>^Each row in a table with a primary key must feature a unique combination
  of values in its primary key columns. ^For the purposes of the previous
  statement, NULL values are considered distinct from all other values,
  including other NULLs. ^If an [INSERT] or [UPDATE] statement attempts to
  modify the table content so that two or more rows feature identical primary
  key values, it is a constraint violation. According to the SQL standard,
  PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a
  long-standing coding oversight, this is not the case in SQLite. ^Unless the
  column is an [INTEGER PRIMARY KEY] SQLite allows NULL values in a PRIMARY KEY
  column.  We could change SQLite to conform to the standard (and we might do
  so in the future), but by the time the oversight was discovered, SQLite was
  in such wide use that we feared breaking legacy code if we fixed the problem.
  So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns.
  Developers should be aware, however, that we may change SQLite to conform to
  the SQL standard in future and should design new programs accordingly.

<p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must feature a unique combination
  of values in the columns identified by the UNIQUE constraint. ^As with
  PRIMARY KEY constraints, for the purposes of UNIQUE constraints NULL values
  are considered distinct from all other values (including other NULLs).
  ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so
  that two or more rows feature identical values in a set of columns that
  are subject to a UNIQUE constraint, it is a constraint violation.

<p>^[INTEGER PRIMARY KEY] columns aside, both UNIQUE and PRIMARY KEY
  constraints are implemented by creating an index in the database (in the same
  way as a [CREATE INDEX|"CREATE UNIQUE INDEX"] statement would). ^This index
  is used like any other index in the database to [optimizer|optimize queries].
  As a result, there often no advantage (but significant overhead) in creating
  an index on a set of columns that are already collectively subject to a
  UNIQUE or PRIMARY KEY constraint.

<p>^(A <b>CHECK</b> 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 as a
  [boolean expression]. If the result is false, then a constraint violation
  has occurred.)^

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

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

<p>Exactly how a constraint violation is dealt with is determined by the
  [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>
<h3>ROWIDs and the INTEGER PRIMARY KEY</h3>

<p>^Every row of every SQLite table has a 64-bit signed integer key 
that is unique within the same table.
This integer is usually called the "rowid".  ^The rowid is the actual key used
in the B-Tree that implements an SQLite table.  ^Rows are stored in

<p>^(When three or more simple SELECTs are connected into a compound SELECT,
they group from left to right. In other words, if "A", "B" and "C" are all
simple SELECT statements, (A op B op C) is processed as ((A op B) op C).)^

</p>

<tcl>hd_fragment orderby {order by}</tcl>
<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>

<p>If a SELECT statement that returns more than one row does not have an
ORDER BY clause, the order in which the rows are returned is undefined.
Or, if a SELECT statement does have an ORDER BY clause, then the list of
expressions attached to the ORDER BY determine the order in which rows
are returned to the user. ^Rows are first sorted based on the results of