Documentation Source Text

RecursiveBubbleDiagram alter-table-stmt
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table,
to rename a column within a table, or to add a new column to an existing table.

<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table}</tcl>
<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table} \
                 {ALTER TABLE RENAME documentation}</tcl>
<h3>ALTER TABLE RENAME</h3>

<p> ^(The RENAME TO syntax changes the name of <yyterm>table-name</yyterm>
to <yyterm>new-table-name</yyterm>.)^
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.
^If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.

<blockquote style='background-color: #ffd0d0;'>
<b>Compatibility Note:</b>
The behavior of ALTER TABLE when renaming a table was enhanced
in versions 3.25.0 ([dateof:3.25.0]) and 3.26.0 ([dateof:3.26.0])
in order to carry the rename operation forward into triggers and
views that reference the renamed table.  This is considered an
improvement. Applications that depend on the older (and
arguably buggy) behavior can use the
[PRAGMA legacy_alter_table=ON] statement to make ALTER TABLE RENAME
[PRAGMA legacy_alter_table=ON] statement or the
[SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] configuration parameter
on [sqlite3_db_config()] interface to make ALTER TABLE RENAME
behave as it did prior to version 3.25.0.
</blockquote>

<p>
Beginning with release 3.25.0 ([dateof:3.25.0]), references to the table
within trigger bodies and view definitions are also renamed.
</p>

    contain code that expect the incomplete behavior
    of [ALTER TABLE RENAME] found in older versions of SQLite.
    New applications should leave this flag turned off.
    <p>For compatibility with older [virtual table] implementations,
    this flag is turned on temporarily while the [sqlite3_module.xRename]
    method is being run.  The value of this flag is restore after the 
    [sqlite3_module.xRename] method finishes.
    <p>The legacy alter table behavior can also be toggled on and off
    using the [SQLITE_DBCONFIG_LEGACY_ALTER_TABLE] option to the
    [sqlite3_db_config()] interface.
}

Pragma legacy_file_format {
   <p>^(<b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format
    flag.)^  ^(When this flag is on, new SQLite databases are created in

[http://site.icu-project.org/|International Components for Unicode]
library.

<tcl>hd_fragment dblquote {double-quoted string literal}</tcl>
<h1>Double-quoted String Literals Are Accepted</h1>

<p>
The SQL standard says that one should use double-quotes around identifiers
and single-quotes around string literals.
The SQL standard requires double-quotes around identifiers
and single-quotes around string literals.  For example:
<ul>
<li> <tt>"this is a legal SQL column name"</tt>
<li> <tt>'this is an SQL string literal'</tt>
</ul>
<p>
SQLite accepts both of the above.  But, in an effort to be compatible
with MySQL 3.x (which was very popular when SQLite was first being
designed) SQLite will also use content contained in double-quotes as a s
tring literal if the content does not match any valid identifier.
with MySQL 3.x (which was probably the most widely used RDBMS
when SQLite was first being designed) SQLite will also use
a double-quotes string as
string literal if it does not match any valid identifier.
<p>
An unfortunate side-effect of this is that a misspelled double-quoted
This misfeature means that a misspelled double-quoted
identifier will be interpreted as a string literal, rather than generating
an error.
Another problem is that this behavior allows developers who are new to
the SQL language to continue using double-quoted string literals when they
It also lures developers who are new to the SQL language into
bad habit of using double-quoted string literals when they
really need to learn to use the correct single-quoted string literal form.
<p>
In hindsight, we should not have tried to make SQLite accept MySQL 3.x
syntax, and should have never allowed double-quoted string literals.
However, there are countless applications that make use of
double-quoted string literals and so we continue to support
However, we continue to support that capability to avoid breaking legacy
that capability to avoid breaking legacy.
applications.
<p>
Updates:
<ul>
Update: As of SQLite 3.27.0 ([dateof:3.27.0]) the use of a double-quoted
<li><p> As of SQLite 3.27.0 ([dateof:3.27.0]) the use of a double-quoted
string literal causes a warning message to be sent to the [error log].
<li><p> As of SQLite 3.29.0 ([dateof:3.29.0]) the use of double-quoted
string literals inside of DDL statements ([CREATE TABLE], [CREATE INDEX],
and so forth) is disallowed and will cause a syntax error.  Double quoted
strings needed to be deactivated in DDL statements as they were causing
problems for [ALTER TABLE].
(See the ticket at [https://www.sqlite.org/src/info/9b78184be266f] for
details.)
If needed for compatibility, the older behavior can be restored by 
disabling the [SQLITE_DBCONFIG_NO_DQS_SCHEMA] option on the
[sqlite3_db_config()] interface.
<li><p> The [SQLITE_DBCONFIG_NO_DQS] option the [sqlite3_db_config()]
interface is available as of SQLite 3.29.0 ([dateof:3.29.0]) and can be
used to disable double-quoted string literals in all contexts.
This setting is currently off by default, but might default on in future
releases of SQLite.  Developers are encouraged to turn this setting on
now, in preparation for the future when it might be activated by default.
</ul>

<h1>Keywords Can Often Be Used As Identifiers</h1>

<p>
The SQL language is rich in keywords.
Most SQL implementations do not allow keywords to be used as identifiers
(the names of table or columns) unless they are enclosed in double-quotes.