syntax.html

sqlite的帮助文档

  If a <a href="c3ref/sqlite3.html">database connection</a> is already in autocommit mode when
  a ROLLBACK statement is invoked, then the statement invocation
  shall fail with an error.
</b></blockquote><a name="H42222"></a>
<p>
  A "pending statement" is a statement for which <a href="c3ref/step.html">sqlite3_step()</a>
  has been called at least once without a subsequent call to
  either <a href="c3ref/reset.html">sqlite3_reset()</a> or <a href="c3ref/finalize.html">sqlite3_finalize()</a>.
</p><blockquote><b>H42222:</b>
  Other pending statements on the same <a href="c3ref/sqlite3.html">database connection</a>
  as a successfully evaluated ROLLBACK statement shall be aborted.
</b></blockquote><a name="H42225"></a>
<blockquote><b>H42225:</b>
  The successful evaluation of a ROLLBACK statement causes the
  current transaction on the <a href="c3ref/sqlite3.html">database connection</a> to roll back.
</b></blockquote>
<p>The <i>transaction_name</i> clause of a BEGIN statement is
provided for syntactic compatibility to other SQL database engines.
The <i>transaction_name</i> clause is silently ignored.</p>


<h2>3.0 Data Definition Language (DDL)</h2>

<h3>3.1 CREATE TABLE</h3>

<a name="H42310"></a>
<blockquote><b>H42310:</b>
  The SQLite parser shall accept CREATE TABLE statements
  that conform to the following syntax:
<blockquote><pre>
cmd ::= CREATE temp TABLE ifnotexists fullname table_definition.
temp ::= .
temp ::= TEMP.
ifnotexists ::= .
ifnotexists ::= IF NOT EXISTS.
</pre></blockquote></b></blockquote>
<p>
  The <i>fullname</i> non-terminal symbol of the SQLite grammar specifies
  an SQL object contained within a particular database file.  A
  specific database might be stated explicitly:

      &lt;<i>fullname ::= databasename DOT objectname.</i>&gt;

  or the database might be implied:

      &lt;<i>fullname ::= objectname.</i>&gt;.

  An unspecified <i>databasename</i> has a default value that
  depends on context and which is specified by requirements.
</p>
<a name="H42313"></a>
<blockquote><b>H42313:</b>
  When the TEMP keyword appears in a CREATE TABLE statement and the
  <i>databasename</i> exists and is something other than "temp", then
  the preparation of the CREATE TABLE statement shall fail with an error.
</b></blockquote><a name="H42316"></a>
<blockquote><b>H42316:</b>
  When the TEMP keyword appears in a CREATE TABLE statement the behavior
  shall be as if the <i>databasename</i> where "temp".
</b></blockquote><a name="H42319"></a>
<blockquote><b>H42319:</b>
  The successful evaluation of a CREATE TABLE statement shall cause
  a new SQL table whose name is given by the <i>objectname</i> to be
  created in the schema of the database whose name is given by the
  <i>databasename</i>.
</b></blockquote><a name="H42322"></a>
<blockquote><b>H42322:</b>
  If a CREATE TABLE statement specifies no <i>databasename</i> and omits
  the TEMP keyword then the behavior shall be as if a <i>databasename</i>
  of "main" where used.
</b></blockquote><a name="H42325"></a>
<blockquote><b>H42325:</b>
  The preparation of a CREATE TABLE statement shall fail with an 
  error if the IF NOT EXISTS clause is omitted and the <i>objectname</i>
  is the same as the name of a table or view
  in the same database.
</b></blockquote><a name="H42328"></a>
<blockquote><b>H42328:</b>
  The evaluation of a CREATE TABLE statement shall be a silent no-op if the
  IF NOT EXISTS clause is present and the <i>objectname</i>
  is the same as the name of a table or view
  in the same database.
</b></blockquote><a name="H42331"></a>
<blockquote><b>H42331:</b>
  The preparation of a CREATE TABLE statement shall fail with an error if the
  the <i>objectname</i> is the same as the name of an index
  in any database attached to the same <a href="c3ref/sqlite3.html">database connection</a>.
</b></blockquote><a name="H42334"></a>
<blockquote><b>H42334:</b>
  The preparation of a CREATE TABLE statement shall fail with an error if the
  the <i>databasename</i> references a database that is not
  attached to the same <a href="c3ref/sqlite3.html">database connection</a>.
</b></blockquote>

<h4>3.1.1 CREATE TABLE column definitions</h4>

<a name="D1"></a>

<p>
  There are two varieties of CREATE TABLE statements.  The most common form
  specifies the names of all columns in the table together with datatype
  and constraint information.  This first form is called a
  "ordinary CREATE TABLE statement".  The second form of CREATE TABLE
  constructs a new table from the result set of a SELECT statement.
  The second form is called a "CREATE TABLE AS statement".
</p>

<a name="H42410"></a>
<p>
  In an ordinary CREATE TABLE statement, the <i>table_definition</i>
  consists of a list of column definitions optionally followed by a 
  list of table constraints.
</p><blockquote><b>H42410:</b>
  The SQLite parser shall accept the following syntax for the 
  <i>table_definition</i> section of a CREATE TABLE statement:
<blockquote><pre>
table_definition ::= LP columnlist constraint_opt RP.
columnlist ::= column.
columnlist ::= columnlist COMMON column.
constraint_opt ::= .
constraint_opt ::= COMMA constraint_list.
</pre></blockquote></b></blockquote><a name="H42413"></a>
<p>
  A column definition within an ordinary CREATE TABLE statement always
  has a column name.  It might also include a datatype for the
  column and zero or more contraints on the column.  But both the
  datatype and the constraints are options.  Other SQL database
  engines also have optional constraints but they usually make the
  datatype required.
</p><blockquote><b>H42413:</b>
  The SQLite parser shall accept the following syntax
  for the <i>column</i> component of a <i>table_definition</i>.
<blockquote><pre>
column ::= name column_type column_constraint_list.
column_type ::= .
column_type ::= typename.
column_type ::= typename LP signed_int RP.
column_type ::= typename LP signed_int COMMA signed_int RP.
typename ::= identifier.
typename ::= typename identifier.
signed_int ::= INTEGER.
signed_int ::= MINUS INTEGER.
signed_int ::= PLUS INTEGER.
</pre></blockquote></b></blockquote><a name="H42416"></a>
<blockquote><b>H42416:</b>
  The preparation of an <a href="syntax.html#D1">ordinary CREATE TABLE statement</a> shall fail 
  with an error if it specifies two or more columns with the same name.
</b></blockquote><a name="H42419"></a>
<blockquote><b>H42419:</b>
  The datatype affinity of each column in a table generate by an
  <a href="syntax.html#D1">ordinary CREATE TABLE statement</a>
  shall be determined from the <i>column_type</i>
  text using the following 5-step algorithm:
  <ol>
  <li><p> If the <i>column_type</i> contains the string "INT"
          then the affinity is INTEGER.</p></li>

  <li><p> Else if the <i>column_type</i> contains one of the strings
         "CHAR", "CLOB", or "TEXT" then the affinity is TEXT. </p></li>

  <li><p> Else if the <i>column_type</i> contains the string "BLOB" 
          or is omitted then the affinity is NONE. </p></li>

  <li><p> Else if the <i>column_type</i> constains one of the strings
          "REAL", "FLOA", or "DOUB" then the affinity
          is REAL. </p></li>

  <li><p> Otherwise the affinity is NUMERIC. </p></li>
  </ol>
</b></blockquote><a name="H42450"></a>
<blockquote><b>H42450:</b>
  The SQLite parser shall accept the following syntax for the list of column
  constraints and modifiers that follows a column definition in 
  an <a href="syntax.html#D1">ordinary CREATE TABLE statement</a>.
<blockquote><pre>
column_constraint_list ::= .
column_constraint_list ::= column_constraint_list named_column_constraint.
named_column_constraint ::= CONSTRAINT name column_constraint.
named_column_constraint ::= column_constraint.
column_constraint ::= DEFAULT term.
column_constraint ::= DEFAULT identifier.
column_constraint ::= DEFAULT PLUS term.
column_constraint ::= DEFAULT MINUS term.
column_constraint ::= DEFAULT LP expr RP.
column_constraint ::= NULL conflict.
column_constraint ::= NOT NULL conflict.
column_constraint ::= PRIMARY KEY sortorder conflict autoincr.
column_constraint ::= UNIQUE conflict.
column_constraint ::= CHECK LP expr RP.
column_constraint ::= COLLATE identifier.
column_constraint ::= foreign_key_constraint.
column_constraint ::= deferrable_constraint.
autoincr ::= .
autoincr ::= AUTOINCR.
indexlist_opt ::= .
indexlist_opt ::= LP indexlist RP.
conflict ::= .
conflict ::= ON CONFLICT IGNORE.
conflict ::= ON CONFLICT REPLACE.
conflict ::= ON CONFLICT ABORT.
conflict ::= ON CONFLICT FAIL.
conflict ::= ON CONFLICT ROLLBACK.
</pre></blockquote></b></blockquote><a name="H42453"></a>
<blockquote><b>H42453:</b>
  When a column as a DEFAULT constraint, the default value for the
  column shall be the value specified by that constraint.
</b></blockquote><a name="H42456"></a>
<blockquote><b>H42456:</b>
  If a column has no DEFAULT constraint then the default value for
  that column shall be NULL.
</b></blockquote><a name="H42459"></a>
<p>
  Every column a table has a "null-conflict resolution behavior"
  which is one of NONE, IGNORE, REPLACE, FAIL, ABORT, or ROLLBACK.</p>

  <p>The default null-conflict resolution behavior is NONE, which means
  NULLs are allowed in the column.  This can be made explicit by
  specifying the NULL constraint.  But the use of the NULL constraint
  is merely a comment for human readers.  SQLite silently ignores the
  NULL constraint and its ON CONFLICT clause.  SQLite only cares
  about NOT NULL constraints since only NOT NULL constraints make
  a behaviorial difference.
</p><blockquote><b>H42459:</b>
  If a column has no NOT NULL constraint then the NULL conflict
  resolution behavior for the column shall be NONE.
</b></blockquote><a name="H42462"></a>
<blockquote><b>H42462:</b>
  If a column has a NOT NULL constraint and that constrait lacks
  an ON CONFLICT clause
  then the null-conflict resolution behavior for the column shall
  be ABORT.
</b></blockquote><a name="H42465"></a>
<blockquote><b>H42465:</b>
  If a column has a NOT NULL constraint and that constraint
  has an ON CONFLICT clause
  then the null-conflict resolution behavior for the column shall
  be the behavior specified by the ON CONFLICT clause.
</b></blockquote><a name="H42467"></a>
<blockquote><b>H42467:</b>
  A column without a COLLATE constraint shall have a default
  collating sequence of BINARY.
</b></blockquote><a name="H42468"></a>
<blockquote><b>H42468:</b>
  A column with a COLLATE constraint shall have a default
  collating sequence as specified by the COLLATE constraint.
</b></blockquote><a name="H42470"></a>
<p>
  The rowid is the underlying key used for storing each row of a 
  table in the B-Tree that implements that table.  Every table has
  a rowid column which is either implicit or explicit.  There can
  be up to four names for the rowid within the same table.  All
  can be used interchangeably.
</p><blockquote><b>H42470:</b>
  If the datatype of a single-column PRIMARY KEY is
  exactly "INTEGER" then the name of that column shall be
  an alias for the table rowid.
</b></blockquote><a name="H42472"></a>
<blockquote><b>H42472:</b>
  If a table contains no column named "ROWID" then "ROWID" shall be
  an alias for the table rowid.
</b></blockquote><a name="H42474"></a>
<blockquote><b>H42474:</b>
  If a table contains no column named "OID" then "OID" shall be
  an alias for the table rowid.
</b></blockquote><a name="H42476"></a>
<blockquote><b>H42476:</b>
  If a table contains no column named "_ROWID_" then "_ROWID_" shall be
  an alias for the table rowid.
</b></blockquote><a name="H42478"></a>
<p>
  The AUTOINCREMENT keyword is ignored except when it appears on an explicit
  rowid column.
</p><blockquote><b>H42478:</b>
  A table shall have an autoincrementing rowid if and only if
  the PRIMARY KEY for the table is an alias for the rowid and
  the PRIMARY KEY declaration uses the AUTOINCR keyword.
</b></blockquote><a name="H42480"></a>
<blockquote><b>H42480:</b>
  Successful evaluation of a CREATE TABLE statement shall create
  an index for every UNIQUE constraint where the created index has
  a conflict resolution algorithm as specified by the ON CONFLICT
  clause of the UNIQUE constraint or a conflict resolution algorithm
  of ABORT if there is no ON CONFLICT clause on the constraint.
</b></blockquote><a name="H42510"></a>
<blockquote><b>H42510:</b>
  The SQLite parser shall accept the following syntax for the list of table
  contraints that occurs at the end of an <a href="syntax.html#D1">ordinary CREATE TABLE statement</a>.
<blockquote><pre>
constraint_list ::= constraint.
constraint_list ::= constraint_list constraint.
constraint_list ::= constraint_list COMMA constraint.
constraint ::= CHECK LP expr RP conflict.
constraint ::= CONSTRAINT name.
constraint ::= FOREIGN KEY LP columnlist RP foreign_key_constraint defer_opt.
constraint ::= PRIMARY KEY LP indexlist autoinc RP conflict.
constraint ::= UNIQUE LP indexlist RP conflict.
</pre></blockquote></b></blockquote><a name="H42513"></a>
<blockquote><b>H42513:</b>
  The preparation of a CREATE TABLE statement that contains more
  than one PRIMARY KEY constraint shall fail with an error.
</b></blockquote><a name="H42516"></a>
<blockquote><b>H42516:</b>
  The preparation of a CREATE TABLE statement that contains a
  CHECK constraint that uses a subquery shall fail with an error.
</b></blockquote><a name="H42517"></a>
<blockquote><b>H42517:</b>
  The preparation of a CREATE TABLE statement that contains a
  CHECK constraint that uses a parameter shall fail with an error.
</b></blockquote><a name="H42518"></a>
<blockquote><b>H42518:</b>
  Name resolution of the <i>expr</i> with each CHECK constraint of
  a CREATE TABLE statement shall be carried out using a name context
  with an empty result set and a source set holding
  single source element that is the
  table being created.
</b></blockquote><a name="H42521"></a>
<blockquote><b>H42521:</b>
  The preparation of a CREATE TABLE statement that contains a
  DEFAULT constraint with an non-constant expression
  shall fail with an error.
</b></blockquote><a name="H42536"></a>
<p>
  Except for the special case of INTEGER PRIMARY KEY, a
  PRIMARY KEY is just an alias for UNIQUE.
</p><blockquote><b>H42536:</b>
  A PRIMARY KEY constraint that does not result in a rowid alias
  shall have the same effect as a UNIQUE constraint.
</b></blockquote><a name="H42539"></a>
<blockquote><b>H42539:</b>
  Preparation of a CREATE TABLE statement shall fail with an
  error if the <i>indexlist</i> of either a table PRIMARY KEY
  or a table UNIQUE constraint references a column that is not