<p>A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locksis available <a href="lockingv3.html">separately</a>.</p><p>The default behavior for SQLite version 3.0.8 is adeferred transaction.  For SQLite version 3.0.0 through 3.0.7,deferred is the only kind of transaction available.  For SQLiteversion 2.8 and earlier, all transactions are exclusive.</p><p>The COMMIT command does not actually perform a commit until allpending SQL commands finish.  Thus if two or more SELECT statementsare in the middle of processing and a COMMIT is executed, the commitwill not actually occur until all SELECT statements finish.</p><p>An attempt to execute COMMIT might result in an SQLITE_BUSY return code.This indicates that another thread or process had a read lock on the databasethat prevented the database from being updated.  When COMMIT fails in thisway, the transaction remains active and the COMMIT can be retried laterafter the reader has had a chance to clear.</p>}Section comment commentSyntax {comment} {<SQL-comment> | <C-comment>} {SQL-comment} {-- <single-line>} {C-comment} {/STAR <multiple-lines> [STAR/]}puts {<p> Comments aren't SQL commands, but can occur in SQL queries. They are treated as whitespace by the parser.  They can begin anywhere whitespace can be found, including inside expressions that span multiple lines.</p><p> SQL comments only extend to the end of the current line.</p><p> C comments can span any number of lines.  If there is no terminatingdelimiter, they extend to the end of the input.  This is not treated asan error.  A new SQL statement can begin on a line after a multilinecomment ends.  C comments can be embedded anywhere whitespace can occur,including inside expressions, and in the middle of other SQL statements.C comments do not nest.  SQL comments inside a C comment will be ignored.</p>}Section COPY copySyntax {sql-statement} {COPY [ OR <conflict-algorithm> ] [<database-name> .] <table-name> FROM <filename>[ USING DELIMITERS <delim> ]}puts {<p>The COPY command is available in SQLite version 2.8 and earlier.The COPY command has been removed from SQLite version 3.0 due tocomplications in trying to support it in a mixed UTF-8/16 environment.In version 3.0, the <a href="sqlite.html">command-line shell</a>contains a new command <b>.import</b> that can be used as a substitutefor COPY.</p><p>The COPY command is an extension used to load large amounts ofdata into a table.  It is modeled after a similar command foundin PostgreSQL.  In fact, the SQLite COPY command is specificallydesigned to be able to read the output of the PostgreSQL dumputility <b>pg_dump</b> so that data can be easily transferred fromPostgreSQL into SQLite.</p><p>The table-name is the name of an existing table which is tobe filled with data.  The filename is a string or identifier thatnames a file from which data will be read.  The filename can bethe <b>STDIN</b> to read data from standard input.</p><p>Each line of the input file is converted into a single recordin the table.  Columns are separated by tabs.  If a tab occurs asdata within a column, then that tab is preceded by a baskslash "\"character.  A baskslash in the data appears as two backslashes ina row.  The optional USING DELIMITERS clause can specify a delimiterother than tab.</p><p>If a column consists of the character "\N", that column is filledwith the value NULL.</p><p>The optional conflict-clause allows the specification of an alternativeconstraint conflict resolution algorithm to use for this one command.See the section titled<a href="#conflict">ON CONFLICT</a> for additional information.</p><p>When the input data source is STDIN, the input can be terminatedby a line that contains only a baskslash and a dot:}puts "\"[Operator \\.]\".</p>"Section {CREATE INDEX} createindexSyntax {sql-statement} {CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> ON <table-name> ( <column-name> [, <column-name>]* )} {column-name} {<name> [ COLLATE <collation-name>] [ ASC | DESC ]}puts {<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followedby the name of the new index, the keyword "ON", the name of a previouslycreated table that is to be indexed, and a parenthesized list of names ofcolumns in the table that are used for the index key.Each column name can be followed by one of the "ASC" or "DESC" keywordsto indicate sort order, but the sort order is ignored in the currentimplementation.  Sorting is always done in ascending order.</p><p>The COLLATE clause following each column name defines a collatingsequence used for text entires in that column.  The default collatingsequence is the collating sequence defined for that column in theCREATE TABLE statement.  Or if no collating sequence is otherwise defined,the built-in BINARY collating sequence is used.</p><p>There are no arbitrary limits on the number of indices that can beattached to a single table, nor on the number of columns in an index.</p><p>If the UNIQUE keyword appears between CREATE and INDEX then duplicateindex entries are not allowed.  Any attempt to insert a duplicate entrywill result in an error.</p><p>The exact textof each CREATE INDEX statement is stored in the <b>sqlite_master</b>or <b>sqlite_temp_master</b> table, depending on whether the tablebeing indexed is temporary.  Every time the database is opened,all CREATE INDEX statementsare read from the <b>sqlite_master</b> table and used to regenerateSQLite's internal representation of the index layout.</p><p>If the optional IF NOT EXISTS clause is present and another indexwith the same name aleady exists, then this command becomes a no-op.</p><p>Indexes are removed with the <a href="#dropindex">DROP INDEX</a> command.</p>}Section {CREATE TABLE} {createtable}Syntax {sql-command} {CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] <table-name> (  <column-def> [, <column-def>]*  [, <constraint>]*)} {sql-command} {CREATE [TEMP | TEMPORARY] TABLE [<database-name>.] <table-name> AS <select-statement>} {column-def} {<name> [<type>] [[CONSTRAINT <name>] <column-constraint>]*} {type} {<typename> |<typename> ( <number> ) |<typename> ( <number> , <number> )} {column-constraint} {NOT NULL [ <conflict-clause> ] |PRIMARY KEY [<sort-order>] [ <conflict-clause> ] [AUTOINCREMENT] |UNIQUE [ <conflict-clause> ] |CHECK ( <expr> ) |DEFAULT <value> |COLLATE <collation-name>} {constraint} {PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |UNIQUE ( <column-list> ) [ <conflict-clause> ] |CHECK ( <expr> )} {conflict-clause} {ON CONFLICT <conflict-algorithm>}puts {<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"followed by the name of a new table and a parenthesized list of columndefinitions and constraints.  The table name can be either an identifieror a string.  Tables names that begin with "<b>sqlite_</b>" are reservedfor use by the engine.</p><p>Each column definition is the name of the column followed by thedatatype for that column, then one or more optional column constraints.The datatype for the column does not restrict what data may be putin that column.See <a href="datatype3.html">Datatypes In SQLite Version 3</a> foradditional information.The UNIQUE constraint causes an index to be created on the specifiedcolumns.  This index must contain unique keys.The COLLATE clause specifies what text <a href="datatype3.html#collation">collating function</a> 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 INSERT.The value may be NULL, a string constant or a number. Starting with version3.1.0, the default value may also be one of the special case-independantkeywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value isNULL, a string constant or number, it is literally inserted into the columnwhenever an INSERT statement that does not specify a value for the column isexecuted. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, thenthe current UTC date and/or time is inserted into the columns. ForCURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The formatfor CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".</p><p>Specifying a PRIMARY KEY normally just creates a UNIQUE indexon the corresponding columns.  However, if primary key is on a single columnthat has datatype INTEGER, then that column is used internallyas the actual key of the B-Tree for the table.  This means that the columnmay only hold unique integer values.  (Except for this one case,SQLite ignores the datatype specification of columns and allowsany kind of data to be put in a column regardless of its declareddatatype.)  If a table does not have an INTEGER PRIMARY KEY column,then the B-Tree key will be a automatically generated integer.  TheB-Tree key for a row can always be accessed using one of thespecial names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".This is true regardless of whether or not there is an INTEGERPRIMARY KEY.  An INTEGER PRIMARY KEY column man also include thekeyword AUTOINCREMENT.  The AUTOINCREMENT keyword modified the waythat B-Tree keys are automatically generated.  Additional detailon automatic B-Tree key generation is available<a href="autoinc.html">separately</a>.</p><p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"and "TABLE" then the table that is created is only visiblewithin that same database connectionand is automatically deleted whenthe database connection is closed.  Any indices created on a temporary tableare also temporary.  Temporary tables and indices are stored in aseparate 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 nodatabase name is specified, and the TEMP keyword is not present,the table is created in the main database.</p><p>The optional conflict-clause following each constraintallows the specification of an alternative defaultconstraint conflict resolution algorithm for that constraint.The default is abort ABORT.  Different constraints within the sametable may have different default conflict resolution algorithms.If an COPY, INSERT, or UPDATE command specifies a different conflictresolution algorithm, then that algorithm is used in place of thedefault algorithm specified in the CREATE TABLE statement.See the section titled<a href="#conflict">ON CONFLICT</a> for additional information.</p><p>CHECK constraints are supported as of version 3.3.0.  Priorto version 3.3.0, CHECK constraints were parsed but not enforced.</p><p>There are no arbitrary limits on the numberof columns or on the number of constraints in a table.The total amount of data in a single row is limited to about1 megabytes in version 2.8.  In version 3.0 there is no arbitrarylimit on the amount of data in a row.</p><p>The CREATE TABLE AS form defines the table to bethe result set of a query.  The names of the table columns arethe names of the columns in the result.</p><p>The exact textof each CREATE TABLE statement is stored in the <b>sqlite_master</b>table.  Every time the database is opened, all CREATE TABLE statementsare read from the <b>sqlite_master</b> table and used to regenerateSQLite's internal representation of the table layout.If the original command was a CREATE TABLE AS then then an equivalentCREATE TABLE statement is synthesized and store in <b>sqlite_master</b>in place of the original command.The text of CREATE TEMPORARY TABLE statements are stored in the<b>sqlite_temp_master</b> table.</p><p>If the optional IF NOT EXISTS clause is present and another tablewith the same name aleady exists, then this command becomes a no-op.</p><p>Tables are removed using the <a href="#droptable">DROP TABLE</a> statement.  </p>}Section {CREATE TRIGGER} createtriggerSyntax {sql-statement} {CREATE [TEMP | TEMPORARY] TRIGGER <trigger-name> [ BEFORE | AFTER ]<database-event> ON [<database-name> .] <table-name><trigger-action>}Syntax {sql-statement} {CREATE [TEMP | TEMPORARY] TRIGGER <trigger-name> INSTEAD OF<database-event> ON [<database-name> .] <view-name><trigger-action>}Syntax {database-event} {DELETE | INSERT | UPDATE | UPDATE OF <column-list>}Syntax {trigger-action} {[ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN <expression> ] BEGIN   <trigger-step> ; [ <trigger-step> ; ]*END}Syntax {trigger-step} {<update-statement> | <insert-statement> | <delete-statement> | <select-statement> }puts {<p>The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations (the <i>trigger-action</i>) that are automatically performed when a specified database event (the<i>database-event</i>) occurs.  </p><p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of aparticular database table occurs, or whenever an UPDATE of one or morespecified columns of a table are updated.</p><p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACHSTATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOREACH ROW implies that the SQL statements specified as <i>trigger-steps</i> may be executed (depending on the WHEN clause) for each database row beinginserted, updated or deleted by the statement causing the trigger to fire.</p><p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of