Documentation Source Text

Check-in [4ffbc3f635]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhanced ALTER TABLE documentation, and in particular show the dangers of not following the generalized 12-step ALTER TABLE procedure precisely.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4ffbc3f635dcd72e2d5169a92ff17a0abbeb1c4371ff9fa5a6eba75dd79de129
User & Date: drh 2018-12-06 15:20:00
Context
2018-12-09
22:31
Omit the sponsor links from the homepage. check-in: 551ce8e2ad user: drh tags: branch-3.26
22:23
Preliminary documentation for VACUUM INTO check-in: 130193a5b9 user: drh tags: trunk
2018-12-06
15:20
Enhanced ALTER TABLE documentation, and in particular show the dangers of not following the generalized 12-step ALTER TABLE procedure precisely. check-in: 4ffbc3f635 user: drh tags: trunk
2018-12-01
20:55
Adjust the date for 3.26.0 in the speed and size chart. check-in: 41f2b76661 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

   118    118   
   119    119   RecursiveBubbleDiagram alter-table-stmt
   120    120   </tcl>
   121    121   
   122    122   <p>SQLite supports a limited subset of ALTER TABLE.
   123    123   The ALTER TABLE command in SQLite allows the user to rename a table,
   124    124   to rename a column within a table, or to add a new column to an existing table.
          125  +
          126  +<tcl>hd_fragment altertabrename {ALTER TABLE RENAME} {rename table}</tcl>
          127  +<h3>ALTER TABLE RENAME</h3>
   125    128   
   126    129   <p> ^(The RENAME TO syntax changes the name of <yyterm>table-name</yyterm>
   127    130   to <yyterm>new-table-name</yyterm>.)^
   128    131   This command 
   129    132   cannot be used to move a table between attached databases, only to rename 
   130    133   a table within the same database.
   131    134   ^If the table being renamed has triggers or indices, then these remain
   132         -attached to the table after it has been renamed.  
          135  +attached to the table after it has been renamed.
          136  +
          137  +<blockquote style='background-color: #ffd0d0;'>
          138  +<b>Compatibility Note:</b>
          139  +The behavior of ALTER TABLE when renaming a table was enhanced
          140  +in versions 3.25.0 ([dateof:3.25.0]) and 3.26.0 ([dateof:3.26.0])
          141  +in order to carry the rename operation forward into triggers and
          142  +views that reference the renamed table.  This is considered an
          143  +improvement. Applications that depend on the older (and
          144  +arguably buggy) behavior can use the
          145  +[PRAGMA legacy_alter_table=ON] statement to make ALTER TABLE RENAME
          146  +behavior as it did prior to version 3.25.0.
          147  +</blockquote>
          148  +
          149  +<p>
   133    150   Beginning with release 3.25.0 ([dateof:3.25.0]), references to the table
   134    151   within trigger bodies and view definitions are also renamed.
   135    152   </p>
          153  +
          154  +<p> Prior to version 3.26.0 ([dateof:3.26.0]), FOREIGN KEY references
          155  +to a table that is renamed were only edited if the
          156  +[PRAGMA foreign_keys=ON], or in other words if
          157  +[foreign key constraints] were begin enforced.  With
          158  +[PRAGMA foreign_keys=OFF], FOREIGN KEY constraints would not be changed
          159  +when the table that the foreign key referred to (the "[parent table]")
          160  +was renamed.  Beginning with version 3.26.0, FOREIGN KEY constraints
          161  +are always converted when a table is renamed, unless the
          162  +[PRAGMA legacy_alter_table=ON] setting is engaged.  The following
          163  +table summaries the difference:</p>
          164  +
          165  +<blockquote>
          166  +<table border="1" cellpadding="2" cellspacing="0">
          167  +<tr>
          168  +<th>PRAGMA foreign_keys
          169  +<th>PRAGMA legacy_alter_table
          170  +<th>[parent table|Parent Table]
          171  +references are updated
          172  +<th>SQLite version
          173  +<tr><td align="center">Off<td align="center">Off<td align="center">No<td align="center">&lt; 3.26.0
          174  +<tr><td align="center">Off<td align="center">Off<td align="center">Yes<td align="center">&gt;= 3.26.0
          175  +<tr><td align="center">On<td align="center">Off<td align="center">Yes<td align="center">all
          176  +<tr><td align="center">Off<td align="center">On<td align="center">No<td align="center">all
          177  +<tr><td align="center">On<td align="center">On<td align="center">Yes<td align="center">all
          178  +</table>
          179  +</blockquote>
          180  +
          181  +<tcl>hd_fragment altertabmvcol {ALTER TABLE RENAME COLUMN} {rename column}</tcl>
          182  +<h3>ALTER TABLE RENAME COLUMN</h3>
   136    183   
   137    184   <p> The RENAME COLUMN TO syntax changes the
   138    185   <yyterm>column-name</yyterm> of table <yyterm>table-name</yyterm>
   139    186   into <yyterm>new-column-name</yyterm>.  The column name is changed both
   140    187   within the table definition itself and also within all indexes, triggers,
   141    188   and views that reference the column.  If the column name change would
   142    189   result in a semantic ambiguity in a trigger or view, then the RENAME
   143    190   COLUMN fails with an error and no changes are applied.
          191  +
          192  +<tcl>hd_fragment altertabaddcol {ALTER TABLE ADD COLUMN} {add column}</tcl>
          193  +<h3>ALTER TABLE ADD COLUMN</h3>
   144    194   
   145    195   <p> ^The ADD COLUMN syntax
   146    196   is used to add a new column to an existing table.
   147    197   ^The new column is always appended to the end of the list of existing columns.
   148    198   The [column-def] rule defines the characteristics of the new column.
   149    199   ^(The new column may take any of the forms permissible in a [CREATE TABLE]
   150    200   statement, with the following restrictions:)^
................................................................................
   173    223   the amount of data in the table.  The ALTER TABLE command runs as quickly
   174    224   on a table with 10 million rows as it does on a table with 1 row.
   175    225   </p>
   176    226   
   177    227   <p>After ADD COLUMN has been run on a database, that database will not
   178    228   be readable by SQLite version 3.1.3 ([dateof:3.1.3]) and earlier.</p>
   179    229   
   180         -<tcl>hd_fragment otheralter</tcl>
          230  +<tcl>hd_fragment otheralter {generalized ALTER TABLE procedure}</tcl>
   181    231   <h3>Making Other Kinds Of Table Schema Changes</h3>
   182    232   
   183    233   <p> The only schema altering commands directly supported by SQLite are the
   184         -"rename table", "rename column", and "add column" commands shown above.  
          234  +"[rename table]", "[rename column]", and "[add column]" commands shown above.  
   185    235   However, applications
   186    236   can make other arbitrary changes to the format of a table using a simple
   187    237   sequence of operations.
   188    238   The steps to make arbitrary changes to the schema design of some table X
   189    239   are as follows:
   190    240   
   191    241   <ol>
   192    242   <li><p>
   193         -If foreign key constraints are enabled,
   194         -disable them using [PRAGMA foreign_keys | PRAGMA foreign_keys=OFF].
          243  +If foreign key constraints are enabled, disable them using [PRAGMA foreign_keys=OFF].
   195    244   
   196    245   <li><p>
   197    246   Start a transaction.
   198    247   
   199    248   <li><p>
   200    249   Remember the format of all indexes and triggers associated with table X.
   201    250   This information will be needed in step 8 below.  One way to do this is
................................................................................
   237    286   <li><p>
   238    287   Commit the transaction started in step 2.
   239    288   
   240    289   <li><p>
   241    290   If foreign keys constraints were originally enabled, reenable them now.
   242    291   </ol>
   243    292   
   244         -<p>The procedure above is completely general and will work even if the
          293  +<tcl>hd_fragment caution</tcl>
          294  +<p>
          295  +<b>Caution:</b>
          296  +Take care to follow the procedure above precisely.  The boxes below
          297  +summarize two procedures for modifying a table definition.  At first
          298  +glance, they both appear to accomplish the same thing.  However, the
          299  +procedure on the right does not always work, especially with the
          300  +enhanced [rename table] capabilities added by versions 3.25.0 and
          301  +3.26.0.  In the procedure on the right, the initial rename of the
          302  +table to a temporary name might corrupt references to that table in
          303  +triggers, views, and foreign key constraints.  The safe procedure on
          304  +the left constructs the revised table definition using a new temporary
          305  +name, then renames the table into its final name, which does not break
          306  +links.
          307  +
          308  +<center>
          309  +<table border="1" cellpadding="10" cellspacing="0">
          310  +<tr>
          311  +<td valign="top">
          312  +<ol>
          313  +<li>Create new table
          314  +<li>Copy data
          315  +<li>Drop old table
          316  +<li>Rename new into old
          317  +</ol>
          318  +<td valign="top">
          319  +<ol>
          320  +<li>Rename old table
          321  +<li>Create new table
          322  +<li>Copy data
          323  +<li>Drop old table
          324  +</ol>
          325  +<tr>
          326  +<th>&uarr;<br>Correct
          327  +<th>&uarr;<br>Incorrect
          328  +</table>
          329  +</center>
          330  +
          331  +<p>The 12-step [generalized ALTER TABLE procedure]
          332  +above will work even if the
   245    333   schema change causes the information stored in the table to change.
   246         -So the full procedure above is appropriate for dropping a column,
          334  +So the full 12-step procedure above is appropriate for dropping a column,
   247    335   changing the order of columns, adding or removing a UNIQUE constraint
   248    336   or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints,
   249    337   or changing the datatype for a column, for example.  However, a simpler
   250    338   and faster procedure can optionally be used for
   251    339   some changes that do no affect the on-disk content in any way.
   252    340   The following simpler procedure is appropriate for removing
   253    341   CHECK or FOREIGN KEY or NOT NULL constraints,

Changes to pages/pragma.in.

   771    771   }
   772    772   
   773    773   
   774    774   Pragma legacy_alter_table {
   775    775      <p>^(<b>PRAGMA legacy_alter_table;
   776    776          <br>PRAGMA legacy_alter_table = <i>boolean</i></b></p>
   777    777       <p>This pragma sets or queries the value of the legacy_alter_table
   778         -    flag.)^  ^(When this flag is on, the ALTER TABLE RENAME
          778  +    flag.)^  ^(When this flag is on, the [ALTER TABLE RENAME]
   779    779       command (for changing the name of a table) works as it did
   780    780       in SQLite 3.24.0 ([dateof:3.24.0]) and earlier.)^  More specifically,
   781    781       when this flag is on
   782         -    the ALTER TABLE RENAME command only rewrites the initial occurrence
   783         -    of the table name in its CREATE TABLE statement and in any associated
   784         -    CREATE INDEX and CREATE TRIGGER statements.  Other references to the
          782  +    the [ALTER TABLE RENAME] command only rewrites the initial occurrence
          783  +    of the table name in its [CREATE TABLE] statement and in any associated
          784  +    [CREATE INDEX] and [CREATE TRIGGER] statements.  Other references to the
   785    785       table are unmodifed, including:
   786    786       <ul>
   787    787       <li> References to the table within the bodies of triggers and views.
   788    788       <li> References to the table within CHECK constraints in the original
   789    789            CREATE TABLE statement.
   790    790       <li> References to the table within the WHERE clauses of [partial indexes].
   791    791       </ul>
   792    792       The default setting for this pragma is OFF, which means that all
   793    793       references to the table anywhere in the schema are converted to the new name.
   794    794       <p>This pragma is provided as a work-around for older programs that
   795    795       contain code that expect the incomplete behavior
   796         -    of ALTER TABLE RENAME found in older versions of SQLite.
          796  +    of [ALTER TABLE RENAME] found in older versions of SQLite.
   797    797       New applications should leave this flag turned off.
   798    798       <p>For compatibility with older [virtual table] implementations,
   799    799       this flag is turned on temporarily while the [sqlite3_module.xRename]
   800    800       method is being run.  The value of this flag is restore after the 
   801    801       [sqlite3_module.xRename] method finishes.
   802    802   }
   803    803