syntax.html

sqlite的帮助文档

  a column in the table.
</b></blockquote><a name="H42570"></a>
<p>
  Foreign key constraints are parsed for compatibility with other
  database engines but are not enforced by SQLite.
</p><blockquote><b>H42570:</b>
  The SQLite parser shall accept the following syntax for a foreign
  key constraint as either a separate constraint or as part of a 
  column constraint in an <a href="syntax.html#D1">ordinary CREATE TABLE statement</a>.
<blockquote><pre>
foreign_key_constraint ::= REFERENCES name indexlist_opt fkarglist.
defer_opt ::= .
defer_opt ::= deferrable_constraint.
deferrable_constraint ::= NOT DEFERRABLE initially_deferred_clause.
deferrable_constraint ::= DEFERRABLE initially_deferred_clause.
fkarglist ::= .
fkarglist ::= fkarglist fkarg.
fkarg ::= MATCH name.
fkarg ::= ON DELETE fkaction.
fkarg ::= ON UPDATE fkaction.
fkarg ::= ON INSERT fkaction.
fkaction ::= SET NULL.
fkaction ::= SET DEFAULT.
fkaction ::= CASCADE.
fkaction ::= RESTRICT.
initially_deferred_clause ::= .
initially_deferred_clause ::= INITIALLY DEFERRED.
initially_deferred_clause ::= INITIALLY IMMEDIATE.
</pre></blockquote></b></blockquote>

<h4>3.1.2 CREATE TABLE AS</h4>

<a name="H42610"></a>
<p>
  The CREATE TABLE AS statement generates a new table to hold the
  result set of a SELECT statement.
</p><blockquote><b>H42610:</b>
  The SQLite parser shall accept the following syntax for creating new
  database tables from the result set of SELECT statements.
<blockquote><pre>
table_definition ::= AS select.
</pre></blockquote></b></blockquote><a name="H42613"></a>
<p>
  The names of the columns in the generated table are taken from the
  column names of the SELECT statement.  All other attributes of the
  generated table are the default attributes for a column.  Hence, the
  table generated by a CREATE TABLE AS as the same behavior as an
  ordinary CREATE TABLE where the <i>table_definition</i> consists of
  a comma-separated list of column names with no datatypes or constraints
  of any kind.
</p><blockquote><b>H42613:</b>
  The table generated by a CREATE TABLE AS statement shall have the
  same number of columns as the result set of the SELECT.
</b></blockquote><a name="H42616"></a>
<blockquote><b>H42616:</b>
  The names of the columns in a table generated by a CREATE TABLE AS
  statement shall have base names which are the names of the columns
  in the result set of the SELECT statement
</b></blockquote><a name="H42617"></a>
<blockquote><b>H42617:</b>
  Each column name in a table generated by a CREATE TABLE AS
  statement shall have an arbitrary suffix appended to its basename
  if and only if such a suffix is necessary to make the name
  distinct from all preceding column names in the table.
</b></blockquote><a name="H42619"></a>
<blockquote><b>H42619:</b>
  All columns in a table generated by a CREATE TABLE AS statement
  shall have a default value of NULL.
</b></blockquote><a name="H42622"></a>
<blockquote><b>H42622:</b>
  All columns in a table generated by a CREATE TABLE AS statement
  shall have a NULL conflict resolution behavior of NONE.
</b></blockquote><a name="H42625"></a>
<blockquote><b>H42625:</b>
  All columns in a table generated by a CREATE TABLE AS statement
  shall have an affinity of NONE.
</b></blockquote><a name="H42628"></a>
<blockquote><b>H42628:</b>
  All columns in a table generated by a CREATE TABLE AS statement
  shall have a default collating sequence of BINARY.
</b></blockquote>


<h3>3.2 DROP TABLE</h3>

<a name="H42700"></a>
<blockquote><b>H42700:</b>
  The SQLite parser shall accept DROP TABLE statements
  that conform to the following syntax.
<blockquote><pre>
cmd ::= DROP TABLE ifexists fullname.
ifexists ::= .
ifexists ::= IF EXISTS.
</pre></blockquote></b></blockquote><a name="H42710"></a>
<blockquote><b>H42710:</b>
  The preparation of a DROP TABLE statement shall fail with an
  error if the statement lacks an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing table.
</b></blockquote><a name="H42713"></a>
<blockquote><b>H42713:</b>
  The evaluation of a DROP TABLE statement shall be a silent no-op
  if the the statement has an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing table.
</b></blockquote><a name="H42716"></a>
<blockquote><b>H42716:</b>
  The successful evaluation of a DROP TABLE statement shall cause
  the table identified by <i>fullname</i> to be removed from its
  database and discarded.
</b></blockquote><a name="H42719"></a>
<blockquote><b>H42719:</b>
  The successful evaluation of a DROP TABLE statement shall cause
  all indices attached to the table identified by <i>fullname</i>
  to be removed from their database and discarded.
</b></blockquote><a name="H42721"></a>
<blockquote><b>H42721:</b>
  The successful evaluation of a DROP TABLE statement shall cause
  all triggers attached to the table identified by <i>fullname</i>
  to be removed from their database and discarded.
</b></blockquote><a name="H42724"></a>
<blockquote><b>H42724:</b>
  The preparation of a DROP TABLE statement shall fail with an
  error if <i>fullname</i> is a system table.
</b></blockquote>

<h3>3.3 CREATE INDEX</h3>

<a name="H42800"></a>
<blockquote><b>H42800:</b>
  The SQLite parser shall accept CREATE INDEX statements that 
  conform to the following syntax:
<blockquote><pre>
cmd ::= CREATE unique INDEX ifnotexists fullname ON tablename LP indexlist RP.
tablename ::= name.
indexlist ::= indexlist COMMA columnname collate sortorder.
indexlist ::= columnname collate sortorder.
columnname ::= name.
collate ::= .
collate ::= COLLATE identifier.
sortorder ::= .
sortorder ::= ASC.
sortorder ::= DESC.
</pre></blockquote></b></blockquote><a name="H42803"></a>
<blockquote><b>H42803:</b>
  The target database of a CREATE INDEX statement shall be the 
  <i>databasename</i> specified in the <i>fullname</i> term of the
  statement if the <i>databasename</i> exists.
</b></blockquote><a name="H42806"></a>
<blockquote><b>H42806:</b>
  If the <i>fullname</i> term of a CREATE INDEX statement does not
  specify a <i>databasename</i> and the <i>tablename</i> references a table 
  that is in the "temp" database, then the target database for the statement
  shall be "temp".
</b></blockquote><a name="H42809"></a>
<blockquote><b>H42809:</b>
  If the <i>fullname</i> term of a CREATE INDEX statement does not
  specify a <i>databasename</i> and the <i>tablename</i> references a table
  that is not in the "temp" database, then the target database for the
  statement shall be "main".
</b></blockquote><a name="H42812"></a>
<blockquote><b>H42812:</b>
  The preparation of a CREATE INDEX statement shall fail with an error if the
  <i>databasename</i> of the <i>fullname</i> exists and references a database 
  that is not attached to the same <a href="c3ref/sqlite3.html">database connection</a>.
</b></blockquote><a name="H42815"></a>
<blockquote><b>H42815:</b>
  The preparation of a CREATE INDEX statement shall fail with an error if the
  <i>tablename</i> does not reference an ordinary table in the
  database of the statement.
</b></blockquote><a name="H42818"></a>
<blockquote><b>H42818:</b>
  A successful evaluation of a CREATE INDEX statement shall create a 
  new index called <i>objectname</i>
  in the database of the statement and attached to the
  table identified by <i>tablename</i> in that same database.
</b></blockquote><a name="H42821"></a>
<blockquote><b>H42821:</b>
  An index generated by a CREATE INDEX statement that omits the
  UNIQUE keyword shall have a conflict resolution behavior
  of NONE.
</b></blockquote><a name="H42824"></a>
<blockquote><b>H42824:</b>
  An index generated by a CREATE INDEX statement that includes the
  UNIQUE keyword shall have a conflict resolution behavior
  of ABORT.
</b></blockquote><a name="H42830"></a>
<blockquote><b>H42830:</b>
  The preparation of a CREATE INDEX statement shall fail with an error if any
  <i>columnname</i> value within the <i>indexlist</i> is not the
  name of one of the columns of the <i>tablename</i> table.
</b></blockquote><a name="H42833"></a>
<p>
  The following rules regarding default collating sequences and
  sort order for indices applies both to indices created by CREATE INDEX
  statements
  and also by UNIQUE or PRIMARY KEY constraints on the table definition.
</p><blockquote><b>H42833:</b>
  The collating sequence for each column of an index shall be the
  collating sequence specified in the <i>indexlist</i>.
</b></blockquote><a name="H42836"></a>
<blockquote><b>H42836:</b>
  If an index column does not specify a collating sequence then
  the collating sequence shall be
  the default collating sequence of the corresponding table column.
</b></blockquote><a name="H42839"></a>
<blockquote><b>H42839:</b>
  The sort order for an index column shall be descending if and only
  if the DESC keyword is used in the <i>indexlist</i> entry for that
  term.
</b></blockquote><a name="H42842"></a>
<blockquote><b>H42842:</b>
  The preparation of a CREATE INDEX statement shall fail with an error
  if the <i>tablename</i> refers to a system table.
</b></blockquote>

<h3>3.4 DROP INDEX</h3>

<a name="H42900"></a>
<blockquote><b>H42900:</b>
  The SQLite parser shall accept DROP INDEX statements 
  that conform to the following syntax:
<blockquote><pre>
cmd ::= DROP INDEX ifexists fullname.
</pre></blockquote></b></blockquote><a name="H42910"></a>
<blockquote><b>H42910:</b>
  The preparation of a DROP INDEX statement shall fail with an
  error if the statement lacks an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing index.
</b></blockquote><a name="H42913"></a>
<blockquote><b>H42913:</b>
  The evaluation of a DROP INDEX statement shall be a silent no-op
  if the the statement has an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing index.
</b></blockquote><a name="H42916"></a>
<blockquote><b>H42916:</b>
  The successful evaluation of a DROP INDEX statement shall cause
  the index identified by <i>fullname</i> to be removed from its
  database and discarded.
</b></blockquote>

<h3>3.5 CREATE VIEW</h3>

<a name="H43100"></a>
<blockquote><b>H43100:</b>
  The SQLite parser shall accept CREATE VIEW statements 
  that conform to the following syntax:
<blockquote><pre>
cmd ::= CREATE temp VIEW ifnotexists fullname AS select.
</pre></blockquote></b></blockquote><a name="H43113"></a>
<blockquote><b>H43113:</b>
  When the TEMP keyword appears in a CREATE VIEW statement and the
  <i>databasename</i> exists and is something other than "temp", then
  the preparation of the CREATE VIEW statement shall fail with an error.
</b></blockquote><a name="H43116"></a>
<blockquote><b>H43116:</b>
  When the TEMP keyword appears in a CREATE VIEW statement the behavior
  shall be as if the <i>databasename</i> where "temp".
</b></blockquote><a name="H43119"></a>
<blockquote><b>H43119:</b>
  The successful evaluation of a CREATE VIEW statement shall cause
  a new view whose name is given by the <i>objectname</i> and is located
  in the schema of the database whose name is given by the
  <i>databasename</i>.
</b></blockquote><a name="H43122"></a>
<blockquote><b>H43122:</b>
  If a CREATE VIEW 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="H43125"></a>
<blockquote><b>H43125:</b>
  The preparation of a CREATE VIEW 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="H43128"></a>
<blockquote><b>H43128:</b>
  The evaluation of a CREATE VIEW 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="H43131"></a>
<blockquote><b>H43131:</b>
  The preparation of a CREATE VIEW 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="H43234"></a>
<blockquote><b>H43234:</b>
  The preparation of a CREATE VIEW 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><a name="H43237"></a>
<blockquote><b>H43237:</b>
  The view generated by a CREATE VIEW statement shall have the
  same number of columns as the result set of the SELECT.
</b></blockquote><a name="H43241"></a>
<blockquote><b>H43241:</b>
  The names of the columns in a view generated by a CREATE VIEW
  statement shall have base names which are the names of the columns
  in the result set of the SELECT statement
</b></blockquote><a name="H43244"></a>
<blockquote><b>H43244:</b>
  Each column name in a table generated by a CREATE VIEW
  statement shall have an arbitrary suffix appended to its basename
  if and only if such a suffix is necessary to make the name
  distinct from all preceding column names in the view.
</b></blockquote>

<h3>3.6 DROP VIEW</h3>

<a name="H43200"></a>
<blockquote><b>H43200:</b>
  The SQLite parser shall accept DROP VIEW statements
  that conform to the following syntax:
<blockquote><pre>
cmd ::= DROP VIEW ifexists fullname.
</pre></blockquote></b></blockquote><a name="H43204"></a>
<blockquote><b>H43204:</b>
  The preparation of a DROP VIEW statement shall fail with an
  error if the statement lacks an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing view.
</b></blockquote><a name="H43207"></a>
<blockquote><b>H43207:</b>
  The evaluation of a DROP VIEW statement shall be a silent no-op
  if the the statement has an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing view.
</b></blockquote><a name="H43211"></a>
<blockquote><b>H43211:</b>
  The successful evaluation of a DROP VIEW statement shall cause
  the view identified by <i>fullname</i> to be removed from its
  database and discarded.
</b></blockquote><a name="H43214"></a>
<blockquote><b>H43214:</b>
  The successful evaluation of a DROP VIEW statement shall cause