BlitzNG
/
database.mod
mirror of https://github.com/bmx-ng/database.mod.git


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232
							<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>SQLite Query Language: CREATE TABLE</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #45735f }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:240px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>CREATE TABLE</h1><h4><a href="syntaxdiagrams.html#create-table-stmt">create-table-stmt:</a></h4><blockquote> <img alt="syntax diagram create-table-stmt" src="images/syntax/create-table-stmt.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#column-def">column-def:</a></h4><blockquote> <img alt="syntax diagram column-def" src="images/syntax/column-def.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#type-name">type-name:</a></h4><blockquote> <img alt="syntax diagram type-name" src="images/syntax/type-name.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#column-constraint">column-constraint:</a></h4><blockquote> <img alt="syntax diagram column-constraint" src="images/syntax/column-constraint.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#table-constraint">table-constraint:</a></h4><blockquote> <img alt="syntax diagram table-constraint" src="images/syntax/table-constraint.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#foreign-key-clause">foreign-key-clause:</a></h4><blockquote> <img alt="syntax diagram foreign-key-clause" src="images/syntax/foreign-key-clause.gif"></img> </blockquote>


<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  
Tables names that begin with "<b>sqlite_</b>" are reserved
for use by SQLite itself and cannot appears in a CREATE TABLE statement.</p>

<p>Each column definition is the name of the column optionally followed by the
datatype for that column, then one or more optional column constraints.
SQLite uses dynamic typing; 
the datatype for the column does not restrict what data may be put
in that column.
The UNIQUE constraint causes an unique index to be created on the specified
columns.  All NULL values are considered different from each other and from
all other values for the purpose of determining uniqueness, hence a UNIQUE
column may contain multiple entries with the value of NULL.
The COLLATE clause specifies what text 
collating function to use when comparing text entries for the column.  
The built-in BINARY collating function is used by default.
<p>
The DEFAULT constraint specifies a default value to use when doing an <a href="lang_insert.html">INSERT</a>.
The value may be NULL, a string constant, a number, or a constant expression
enclosed in parentheses.
The default value may also be one of the special case-independant
keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is
NULL, a string constant or number, it is literally inserted into the column
whenever an INSERT statement that does not specify a value for the column is
executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then
the current UTC date and/or time is inserted into the columns. For
CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format
for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
</p>

<p>The PRIMARY KEY attribute normally creates a UNIQUE index on
the column or columns that are specified as the PRIMARY KEY.  The only
exception to this behavior is special <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column,
described below.
According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not 
the case in SQLite.  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>

<p>SQLite uses dynamic typing instead of static typing.  Except for the
special case of <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, SQLite will allow values of any
type to be stored in any column regardless of the declared datatype of
that column.  The declared datatype is a type affinity that
SQLite attempts to comply with, but the operation will proceed even if
compliance is not possible.</p>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible
within that same database connection
and is automatically deleted when
the database connection is closed.  Any indices created on a temporary table
are also temporary.  Temporary tables and indices are stored in a
separate file distinct from the main database file.</p>

<p> If a &lt;database-name&gt; is specified, then the table is created in 
the named database. It is an error to specify both a &lt;database-name&gt;
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>The optional <a href="lang_conflict.html">conflict clause</a> following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.
If no conflict clause is specified, the ABORT algorithm is used.
Different constraints within the same
table may have different conflict resolution algorithms.
If an <a href="lang_insert.html">INSERT</a> or <a href="lang_update.html">UPDATE</a> statement specifies a conflict
resolution algorithm, then the algorithm specified on the INSERT or
UPDATE statement overrides the algorithm specified in the 
CREATE TABLE statement.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>

<p>CHECK constraints are supported as of version 3.3.0.  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

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


<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>If the optional IF NOT EXISTS clause is present and another table
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> 
statement.  </p>

<a name="rowid"></a>

<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
rowid order. The
rowid value can be accessed using one of the special names
"<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".</p>

<p>
If a column is declared to be an INTEGER PRIMARY KEY, then that column is not
a "real" database column but instead becomes
an alias for the rowid.  Unlike normal SQLite columns, the rowid
must be a non-NULL integer value.  The rowid is not able to hold
floating point values, strings, BLOBs, or NULLs.</p>

<blockquote><i>
An INTEGER PRIMARY KEY column is an alias for the 64-bit signed integer rowid.
</i></blockquote>

<p>An INTEGER PRIMARY KEY column can also include the
keyword AUTOINCREMENT.  The AUTOINCREMENT keyword modified the way
that B-Tree keys are automatically generated.  Additional detail
on automatic B-Tree key generation is available
separately.</p>

<p>The special behavior of INTEGER PRIMARY KEY
is only available if the type name is exactly "INTEGER" (in any mixture
of upper and lower case.)  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.
The special behavior of INTEGER PRIMARY KEY is only available if the
primary key is a single column.  Multi-column primary keys do not become
aliases for the rowid.
The AUTOINCREMENT keyword only works on a column that is an alias
for the rowid.</p>

<p>Note that searches against a rowid are generally about twice as
fast as searches against any other PRIMARY KEY or indexed value.
</p>

<p><b>Goofy behavior alert:</b>  The following three declarations all cause
the column "x" be an alias for the rowid:</p>

<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, in contrast, the following declaration does <u>not</u> result in
"x" being an alias for the rowid:</p>

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

<p>This asymmetrical behavior is unfortunate and is really due to a bug
in the parser in early versions of SQLite.  But fixing the bug would
result in very serious backwards incompatibilities.  The SQLite developers
feel that goofy behavior in an corner case is far better than
a compatibility break, so the original behavior is retained.</p>