System.Data.SQLite
Artifact [33801176ff]
Not logged in

Artifact 33801176ff59e73c35ce3f58dbd2ba90bc1e7246:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite Query Language: CREATE TABLE</title>
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_div("searchmenu")'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<span class="desktoponly">Search for:</span> <input type="text" name="q">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
</script>
</div>
<div class=nosearch><h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>CREATE TABLE</h2></div><p><b><a href="syntax/create-table-stmt.html">create-table-stmt:</a></b>
<button id='x861' onclick='hideorshow("x861","x862")'>hide</button></p>
 <div id='x862' class='imgcontainer'>
 <img alt="syntax diagram create-table-stmt" src="images/syntax/create-table-stmt.gif" />
<p><b><a href="syntax/column-def.html">column-def:</a></b>
<button id='x863' onclick='hideorshow("x863","x864")'>show</button></p>
 <div id='x864' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram column-def" src="images/syntax/column-def.gif" />
<p><b><a href="syntax/column-constraint.html">column-constraint:</a></b>
<button id='x865' onclick='hideorshow("x865","x866")'>show</button></p>
 <div id='x866' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram column-constraint" src="images/syntax/column-constraint.gif" />
<p><b><a href="syntax/conflict-clause.html">conflict-clause:</a></b>
<button id='x867' onclick='hideorshow("x867","x868")'>show</button></p>
 <div id='x868' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram conflict-clause" src="images/syntax/conflict-clause.gif" />
</div>
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x869' onclick='hideorshow("x869","x870")'>show</button></p>
 <div id='x870' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x871' onclick='hideorshow("x871","x872")'>show</button></p>
 <div id='x872' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</div>
</div>
<p><b><a href="syntax/foreign-key-clause.html">foreign-key-clause:</a></b>
<button id='x873' onclick='hideorshow("x873","x874")'>show</button></p>
 <div id='x874' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram foreign-key-clause" src="images/syntax/foreign-key-clause.gif" />
</div>
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x875' onclick='hideorshow("x875","x876")'>show</button></p>
 <div id='x876' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</div>
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x877' onclick='hideorshow("x877","x878")'>show</button></p>
 <div id='x878' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</div>
</div>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x879' onclick='hideorshow("x879","x880")'>show</button></p>
 <div id='x880' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram type-name" src="images/syntax/type-name.gif" />
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x881' onclick='hideorshow("x881","x882")'>show</button></p>
 <div id='x882' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</div>
</div>
</div>
<p><b><a href="syntax/select-stmt.html">select-stmt:</a></b>
<button id='x883' onclick='hideorshow("x883","x884")'>show</button></p>
 <div id='x884' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram select-stmt" src="images/syntax/select-stmt.gif" />
<p><b><a href="syntax/common-table-expression.html">common-table-expression:</a></b>
<button id='x885' onclick='hideorshow("x885","x886")'>show</button></p>
 <div id='x886' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram common-table-expression" src="images/syntax/common-table-expression.gif" />
</div>
<p><b><a href="syntax/compound-operator.html">compound-operator:</a></b>
<button id='x887' onclick='hideorshow("x887","x888")'>show</button></p>
 <div id='x888' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram compound-operator" src="images/syntax/compound-operator.gif" />
</div>
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x889' onclick='hideorshow("x889","x890")'>show</button></p>
 <div id='x890' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x891' onclick='hideorshow("x891","x892")'>show</button></p>
 <div id='x892' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</div>
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x893' onclick='hideorshow("x893","x894")'>show</button></p>
 <div id='x894' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</div>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x895' onclick='hideorshow("x895","x896")'>show</button></p>
 <div id='x896' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram type-name" src="images/syntax/type-name.gif" />
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x897' onclick='hideorshow("x897","x898")'>show</button></p>
 <div id='x898' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</div>
</div>
</div>
<p><b><a href="syntax/join-clause.html">join-clause:</a></b>
<button id='x899' onclick='hideorshow("x899","x900")'>show</button></p>
 <div id='x900' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-clause" src="images/syntax/join-clause.gif" />
<p><b><a href="syntax/join-constraint.html">join-constraint:</a></b>
<button id='x901' onclick='hideorshow("x901","x902")'>show</button></p>
 <div id='x902' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-constraint" src="images/syntax/join-constraint.gif" />
</div>
<p><b><a href="syntax/join-operator.html">join-operator:</a></b>
<button id='x903' onclick='hideorshow("x903","x904")'>show</button></p>
 <div id='x904' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-operator" src="images/syntax/join-operator.gif" />
</div>
</div>
<p><b><a href="syntax/ordering-term.html">ordering-term:</a></b>
<button id='x905' onclick='hideorshow("x905","x906")'>show</button></p>
 <div id='x906' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram ordering-term" src="images/syntax/ordering-term.gif" />
</div>
<p><b><a href="syntax/result-column.html">result-column:</a></b>
<button id='x907' onclick='hideorshow("x907","x908")'>show</button></p>
 <div id='x908' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram result-column" src="images/syntax/result-column.gif" />
</div>
<p><b><a href="syntax/table-or-subquery.html">table-or-subquery:</a></b>
<button id='x909' onclick='hideorshow("x909","x910")'>show</button></p>
 <div id='x910' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram table-or-subquery" src="images/syntax/table-or-subquery.gif" />
</div>
</div>
<p><b><a href="syntax/table-constraint.html">table-constraint:</a></b>
<button id='x911' onclick='hideorshow("x911","x912")'>show</button></p>
 <div id='x912' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram table-constraint" src="images/syntax/table-constraint.gif" />
<p><b><a href="syntax/conflict-clause.html">conflict-clause:</a></b>
<button id='x913' onclick='hideorshow("x913","x914")'>show</button></p>
 <div id='x914' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram conflict-clause" src="images/syntax/conflict-clause.gif" />
</div>
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x915' onclick='hideorshow("x915","x916")'>show</button></p>
 <div id='x916' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x917' onclick='hideorshow("x917","x918")'>show</button></p>
 <div id='x918' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</div>
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x919' onclick='hideorshow("x919","x920")'>show</button></p>
 <div id='x920' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</div>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x921' onclick='hideorshow("x921","x922")'>show</button></p>
 <div id='x922' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram type-name" src="images/syntax/type-name.gif" />
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x923' onclick='hideorshow("x923","x924")'>show</button></p>
 <div id='x924' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</div>
</div>
</div>
<p><b><a href="syntax/foreign-key-clause.html">foreign-key-clause:</a></b>
<button id='x925' onclick='hideorshow("x925","x926")'>show</button></p>
 <div id='x926' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram foreign-key-clause" src="images/syntax/foreign-key-clause.gif" />
</div>
<p><b><a href="syntax/indexed-column.html">indexed-column:</a></b>
<button id='x927' onclick='hideorshow("x927","x928")'>show</button></p>
 <div id='x928' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram indexed-column" src="images/syntax/indexed-column.gif" />
</div>
</div>
</div>


<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> Whether the table is a <a href="withoutrowid.html">WITHOUT ROWID</a> 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 <span class='yyterm'>schema-name</span> is specified, it must be either "main", 
  "temp", or the name of an <a href="lang_attach.html">attached database</a>. 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 
  <span class='yyterm'>schema-name</span> and the TEMP or TEMPORARY keyword, unless the
  <span class='yyterm'>schema-name</span> 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 <a href="lang_createtrigger.html">trigger</a>.

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

<a name="createtabas"></a>

<h3>CREATE TABLE ... AS SELECT Statements</h3>

<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 <a href="datatype3.html#expraff">expression affinity</a> 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 <a href="lang_createtable.html#rowid">rowid</a> values, starting with 1, in the <a href="lang_select.html#orderby">order</a> that they
are returned by the SELECT statement.

<a name="tablecoldef"></a>

<h3>Column Definitions</h3>

<p>Unless it is a CREATE TABLE ... AS SELECT statement, a CREATE TABLE includes
one or more <a href="syntax/column-def.html">column definitions</a>, optionally followed by a list of
<a href="syntax/table-constraint.html">table constraints</a>.  Each column definition consists of the
name of the column, optionally followed by the declared type of the column,
then one or more optional <a href="syntax/column-constraint.html">column constraints</a>. 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 <a href="lang_createtable.html#constraints">SQL Data Constraints</a> 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 <a href="datatype3.html">dynamic typing</a>. The declared type of a column is used to
determine the <a href="datatype3.html#affinity">affinity</a> of the column only.

<a name="dfltval"></a>

<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 <a href="lang_insert.html">INSERT</a>. 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 does
contains no sub-queries, column or table references, <a href="lang_expr.html#varparam">bound parameters</a>,
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>

<p>The COLLATE clause specifies the name of a <a href="datatype3.html#collation">collating sequence</a> to use as
the default collation sequence for the column. If no COLLATE clause is
specified, the default collation sequence is <a href="datatype3.html#collation">BINARY</a>.

<p>The number of columns in a table is limited by the <a href="limits.html#max_column">SQLITE_MAX_COLUMN</a>
compile-time parameter. A single row of a table cannot store more than
<a href="limits.html#max_length">SQLITE_MAX_LENGTH</a> bytes of data. Both of these limits can be lowered at
runtime using the <a href="c3ref/limit.html">sqlite3_limit()</a> C/C++ interface.</p>

<a name="constraints"></a>

<h3>SQL Data Constraints</h3>

<a name="primkeyconst"></a>

<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 <a href="syntax/table-constraint.html">table-constraint</a>, 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 <a href="syntax/indexed-column.html">indexed-column</a> 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 <a href="withoutrowid.html">WITHOUT ROWID</a> 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 <a href="withoutrowid.html">WITHOUT ROWID</a> table,
  then the column is known as an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>.
  See <a href="lang_createtable.html#rowid">below</a> for a description of the special properties and behaviors
  associated with an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>.

<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 <a href="lang_insert.html">INSERT</a> or <a href="lang_update.html">UPDATE</a>
  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 <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> or
  the table is a <a href="withoutrowid.html">WITHOUT ROWID</a> 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.

<a name="uniqueconst"></a>

<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 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 <a href="syntax/table-constraint.html">table-constraint</a> clause must contain
  only column names &mdash; the use of 
  expressions in an <a href="syntax/indexed-column.html">indexed-column</a> of a UNIQUE <a href="syntax/table-constraint.html">table-constraint</a>
  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 <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> and PRIMARY KEYs on 
  <a href="withoutrowid.html">WITHOUT ROWID</a> 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>

<a name="ckconst"></a>

<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 and
  cast to a NUMERIC value in the same way as a <a href="lang_expr.html#castexpr">CAST expression</a>. 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.

<a name="notnullconst"></a>

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

<p>Exactly how a constraint violation is dealt with is determined by the
  <a href="lang_conflict.html">constraint conflict resolution algorithm</a>. 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 <a href="syntax/conflict-clause.html">conflict-clause</a> in their definitions. Or, if a constraint definition
  does not include a <a href="syntax/conflict-clause.html">conflict-clause</a> 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 <a href="lang_conflict.html">ON CONFLICT</a> for additional information.

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

<h3>ROWIDs and the INTEGER PRIMARY KEY</h3>

<p>Except for <a href="withoutrowid.html">WITHOUT ROWID</a> 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 <a href="withoutrowid.html">WITHOUT ROWID</a> tables.
WITHOUT ROWID tables are only available in SQLite <a href="releaselog/3_8_2.html">version 3.8.2</a>
(2013-12-06) 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 <a href="datatype3.html#affinity">affinity</a> 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 <a href="foreignkeys.html#parentchild">parent key</a> of a <a href="foreignkeys.html">foreign key constraint</a> is not allowed to
use the rowid.  The parent key must used named columns only.</p>