File: lang_createtable.html

package info (click to toggle)
sqlite3 3.8.7.1-1
links: PTS
area: main
in suites: jessie-kfreebsd
size: 39,012 kB
sloc: ansic: 150,070; sh: 20,920; tcl: 11,058; makefile: 1,290; yacc: 1,093; awk: 268
file content (620 lines) | stat: -rw-r--r-- 31,020 bytes
parent folder | download | duplicates (2)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>SQLite Query Language: CREATE TABLE</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #044a64 }
a:visited { color: #734559 }

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

.menubar {
  clear: both;
  border-radius: 8px;
  background: #044a64;
  padding: 0px;
  margin: 0px;
  cell-spacing: 0px;
}    
.toolbar {
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding: 0px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #044a64; 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; }

/* Things for "fancyformat" documents start here. */
.fancy img+p {font-style:italic}
.fancy .codeblock i { color: darkblue; }
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
.fancy h2 { margin-left: 10px }
.fancy h3 { margin-left: 20px }
.fancy h4 { margin-left: 30px }
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
.fancy #toc a        { color: darkblue ; text-decoration: none }
.fancy .todo         { color: #AA3333 ; font-style : italic }
.fancy .todo:before  { content: 'TODO:' }
.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 { margin: 1em 5ex }
.fancy li p { margin: 1em 0 }
/* End of "fancyformat" specific rules. */

</style>
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
 border="0"></a>
<div><!-- IE hack to prevent disappearing logo--></div>
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>

<table width=100% class="menubar"><tr>
  <td width=100%>
  <div class="toolbar">
    <a href="about.html">About</a>
    <a href="sitemap.html">Sitemap</a>
    <a href="docs.html">Documentation</a>
    <a href="download.html">Download</a>
    <a href="copyright.html">License</a>
    <a href="news.html">News</a>
    <a href="support.html">Support</a>
  </div>
<script>
  gMsg = "Search SQLite Docs..."
  function entersearch() {
    var q = document.getElementById("q");
    if( q.value == gMsg ) { q.value = "" }
    q.style.color = "black"
    q.style.fontStyle = "normal"
  }
  function leavesearch() {
    var q = document.getElementById("q");
    if( q.value == "" ) { 
      q.value = gMsg
      q.style.color = "#044a64"
      q.style.fontStyle = "italic"
    }
  }
  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>
<td>
    <div style="padding:0 1em 0px 0;white-space:nowrap">
    <form name=f method="GET" action="http://www.sqlite.org/search">
      <input id=q name=q type=text
       onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
      <input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
    </form>
    </div>
  </table>

<div class=startsearch></div>
  
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>CREATE TABLE</h2><p><b><a href="syntax/create-table-stmt.html">create-table-stmt:</a></b>
<button id='x855' onclick='hideorshow("x855","x856")'>hide</button></p>
 <blockquote id='x856'>
 <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='x857' onclick='hideorshow("x857","x858")'>show</button></p>
 <blockquote id='x858' style='display:none;'>
 <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='x859' onclick='hideorshow("x859","x860")'>show</button></p>
 <blockquote id='x860' style='display:none;'>
 <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='x861' onclick='hideorshow("x861","x862")'>show</button></p>
 <blockquote id='x862' style='display:none;'>
 <img alt="syntax diagram conflict-clause" src="images/syntax/conflict-clause.gif" />
</blockquote>
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x863' onclick='hideorshow("x863","x864")'>show</button></p>
 <blockquote id='x864' style='display:none;'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x865' onclick='hideorshow("x865","x866")'>show</button></p>
 <blockquote id='x866' style='display:none;'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</blockquote>
</blockquote>
<p><b><a href="syntax/foreign-key-clause.html">foreign-key-clause:</a></b>
<button id='x867' onclick='hideorshow("x867","x868")'>show</button></p>
 <blockquote id='x868' style='display:none;'>
 <img alt="syntax diagram foreign-key-clause" src="images/syntax/foreign-key-clause.gif" />
</blockquote>
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x869' onclick='hideorshow("x869","x870")'>show</button></p>
 <blockquote id='x870' style='display:none;'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</blockquote>
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x871' onclick='hideorshow("x871","x872")'>show</button></p>
 <blockquote id='x872' style='display:none;'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</blockquote>
</blockquote>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x873' onclick='hideorshow("x873","x874")'>show</button></p>
 <blockquote id='x874' style='display:none;'>
 <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='x875' onclick='hideorshow("x875","x876")'>show</button></p>
 <blockquote id='x876' style='display:none;'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</blockquote>
</blockquote>
</blockquote>
<p><b><a href="syntax/select-stmt.html">select-stmt:</a></b>
<button id='x877' onclick='hideorshow("x877","x878")'>show</button></p>
 <blockquote id='x878' style='display:none;'>
 <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='x879' onclick='hideorshow("x879","x880")'>show</button></p>
 <blockquote id='x880' style='display:none;'>
 <img alt="syntax diagram common-table-expression" src="images/syntax/common-table-expression.gif" />
</blockquote>
<p><b><a href="syntax/compound-operator.html">compound-operator:</a></b>
<button id='x881' onclick='hideorshow("x881","x882")'>show</button></p>
 <blockquote id='x882' style='display:none;'>
 <img alt="syntax diagram compound-operator" src="images/syntax/compound-operator.gif" />
</blockquote>
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x883' onclick='hideorshow("x883","x884")'>show</button></p>
 <blockquote id='x884' style='display:none;'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x885' onclick='hideorshow("x885","x886")'>show</button></p>
 <blockquote id='x886' style='display:none;'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</blockquote>
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x887' onclick='hideorshow("x887","x888")'>show</button></p>
 <blockquote id='x888' style='display:none;'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</blockquote>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x889' onclick='hideorshow("x889","x890")'>show</button></p>
 <blockquote id='x890' style='display:none;'>
 <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='x891' onclick='hideorshow("x891","x892")'>show</button></p>
 <blockquote id='x892' style='display:none;'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</blockquote>
</blockquote>
</blockquote>
<p><b><a href="syntax/join-clause.html">join-clause:</a></b>
<button id='x893' onclick='hideorshow("x893","x894")'>show</button></p>
 <blockquote id='x894' style='display:none;'>
 <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='x895' onclick='hideorshow("x895","x896")'>show</button></p>
 <blockquote id='x896' style='display:none;'>
 <img alt="syntax diagram join-constraint" src="images/syntax/join-constraint.gif" />
</blockquote>
<p><b><a href="syntax/join-operator.html">join-operator:</a></b>
<button id='x897' onclick='hideorshow("x897","x898")'>show</button></p>
 <blockquote id='x898' style='display:none;'>
 <img alt="syntax diagram join-operator" src="images/syntax/join-operator.gif" />
</blockquote>
</blockquote>
<p><b><a href="syntax/ordering-term.html">ordering-term:</a></b>
<button id='x899' onclick='hideorshow("x899","x900")'>show</button></p>
 <blockquote id='x900' style='display:none;'>
 <img alt="syntax diagram ordering-term" src="images/syntax/ordering-term.gif" />
</blockquote>
<p><b><a href="syntax/result-column.html">result-column:</a></b>
<button id='x901' onclick='hideorshow("x901","x902")'>show</button></p>
 <blockquote id='x902' style='display:none;'>
 <img alt="syntax diagram result-column" src="images/syntax/result-column.gif" />
</blockquote>
<p><b><a href="syntax/table-or-subquery.html">table-or-subquery:</a></b>
<button id='x903' onclick='hideorshow("x903","x904")'>show</button></p>
 <blockquote id='x904' style='display:none;'>
 <img alt="syntax diagram table-or-subquery" src="images/syntax/table-or-subquery.gif" />
</blockquote>
</blockquote>
<p><b><a href="syntax/table-constraint.html">table-constraint:</a></b>
<button id='x905' onclick='hideorshow("x905","x906")'>show</button></p>
 <blockquote id='x906' style='display:none;'>
 <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='x907' onclick='hideorshow("x907","x908")'>show</button></p>
 <blockquote id='x908' style='display:none;'>
 <img alt="syntax diagram conflict-clause" src="images/syntax/conflict-clause.gif" />
</blockquote>
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x909' onclick='hideorshow("x909","x910")'>show</button></p>
 <blockquote id='x910' style='display:none;'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x911' onclick='hideorshow("x911","x912")'>show</button></p>
 <blockquote id='x912' style='display:none;'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</blockquote>
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x913' onclick='hideorshow("x913","x914")'>show</button></p>
 <blockquote id='x914' style='display:none;'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</blockquote>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x915' onclick='hideorshow("x915","x916")'>show</button></p>
 <blockquote id='x916' style='display:none;'>
 <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='x917' onclick='hideorshow("x917","x918")'>show</button></p>
 <blockquote id='x918' style='display:none;'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</blockquote>
</blockquote>
</blockquote>
<p><b><a href="syntax/foreign-key-clause.html">foreign-key-clause:</a></b>
<button id='x919' onclick='hideorshow("x919","x920")'>show</button></p>
 <blockquote id='x920' style='display:none;'>
 <img alt="syntax diagram foreign-key-clause" src="images/syntax/foreign-key-clause.gif" />
</blockquote>
<p><b><a href="syntax/indexed-column.html">indexed-column:</a></b>
<button id='x921' onclick='hideorshow("x921","x922")'>show</button></p>
 <blockquote id='x922' style='display:none;'>
 <img alt="syntax diagram indexed-column" src="images/syntax/indexed-column.gif" />
</blockquote>
</blockquote>
</blockquote>


<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 &lt;database-name&gt; 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 
  &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 <a href="lang_createtrigger.html">trigger</a>.

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

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

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

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