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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
4ffbc3f635dcd72e2d5169a92ff17a0a |
User & Date: | drh 2018-12-06 15:20:00.145 |
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
Changes to pages/lang.in.
︙ | ︙ | |||
118 119 120 121 122 123 124 125 126 127 128 129 130 131 | 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. <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 | > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 | 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> <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 behavior 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> <p> Prior to version 3.26.0 ([dateof:3.26.0]), FOREIGN KEY references to a table that is renamed were only edited if the [PRAGMA foreign_keys=ON], or in other words if [foreign key constraints] were begin enforced. With [PRAGMA foreign_keys=OFF], FOREIGN KEY constraints would not be changed when the table that the foreign key referred to (the "[parent table]") was renamed. Beginning with version 3.26.0, FOREIGN KEY constraints are always converted when a table is renamed, unless the [PRAGMA legacy_alter_table=ON] setting is engaged. The following table summaries the difference:</p> <blockquote> <table border="1" cellpadding="2" cellspacing="0"> <tr> <th>PRAGMA foreign_keys <th>PRAGMA legacy_alter_table <th>[parent table|Parent Table] references are updated <th>SQLite version <tr><td align="center">Off<td align="center">Off<td align="center">No<td align="center">< 3.26.0 <tr><td align="center">Off<td align="center">Off<td align="center">Yes<td align="center">>= 3.26.0 <tr><td align="center">On<td align="center">Off<td align="center">Yes<td align="center">all <tr><td align="center">Off<td align="center">On<td align="center">No<td align="center">all <tr><td align="center">On<td align="center">On<td align="center">Yes<td align="center">all </table> </blockquote> <tcl>hd_fragment altertabmvcol {ALTER TABLE RENAME COLUMN} {rename column}</tcl> <h3>ALTER TABLE RENAME COLUMN</h3> <p> The RENAME COLUMN TO syntax changes the <yyterm>column-name</yyterm> of table <yyterm>table-name</yyterm> into <yyterm>new-column-name</yyterm>. The column name is changed both within the table definition itself and also within all indexes, triggers, and views that reference the column. If the column name change would result in a semantic ambiguity in a trigger or view, then the RENAME COLUMN fails with an error and no changes are applied. <tcl>hd_fragment altertabaddcol {ALTER TABLE ADD COLUMN} {add column}</tcl> <h3>ALTER TABLE ADD COLUMN</h3> <p> ^The ADD COLUMN syntax is used to add a new column to an existing table. ^The new column is always appended to the end of the list of existing columns. The [column-def] rule defines the characteristics of the new column. ^(The new column may take any of the forms permissible in a [CREATE TABLE] statement, with the following restrictions:)^ |
︙ | ︙ | |||
173 174 175 176 177 178 179 | the amount of data in the table. The ALTER TABLE command runs as quickly on a table with 10 million rows as it does on a table with 1 row. </p> <p>After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 ([dateof:3.1.3]) and earlier.</p> | | | | < | 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 | the amount of data in the table. The ALTER TABLE command runs as quickly on a table with 10 million rows as it does on a table with 1 row. </p> <p>After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 ([dateof:3.1.3]) and earlier.</p> <tcl>hd_fragment otheralter {generalized ALTER TABLE procedure}</tcl> <h3>Making Other Kinds Of Table Schema Changes</h3> <p> The only schema altering commands directly supported by SQLite are the "[rename table]", "[rename column]", and "[add column]" commands shown above. However, applications can make other arbitrary changes to the format of a table using a simple sequence of operations. The steps to make arbitrary changes to the schema design of some table X are as follows: <ol> <li><p> If foreign key constraints are enabled, disable them using [PRAGMA foreign_keys=OFF]. <li><p> Start a transaction. <li><p> Remember the format of all indexes and triggers associated with table X. This information will be needed in step 8 below. One way to do this is |
︙ | ︙ | |||
237 238 239 240 241 242 243 | <li><p> Commit the transaction started in step 2. <li><p> If foreign keys constraints were originally enabled, reenable them now. </ol> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 | <li><p> Commit the transaction started in step 2. <li><p> If foreign keys constraints were originally enabled, reenable them now. </ol> <tcl>hd_fragment caution</tcl> <p> <b>Caution:</b> Take care to follow the procedure above precisely. The boxes below summarize two procedures for modifying a table definition. At first glance, they both appear to accomplish the same thing. However, the procedure on the right does not always work, especially with the enhanced [rename table] capabilities added by versions 3.25.0 and 3.26.0. In the procedure on the right, the initial rename of the table to a temporary name might corrupt references to that table in triggers, views, and foreign key constraints. The safe procedure on the left constructs the revised table definition using a new temporary name, then renames the table into its final name, which does not break links. <center> <table border="1" cellpadding="10" cellspacing="0"> <tr> <td valign="top"> <ol> <li>Create new table <li>Copy data <li>Drop old table <li>Rename new into old </ol> <td valign="top"> <ol> <li>Rename old table <li>Create new table <li>Copy data <li>Drop old table </ol> <tr> <th>↑<br>Correct <th>↑<br>Incorrect </table> </center> <p>The 12-step [generalized ALTER TABLE procedure] above will work even if the schema change causes the information stored in the table to change. So the full 12-step procedure above is appropriate for dropping a column, changing the order of columns, adding or removing a UNIQUE constraint or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints, or changing the datatype for a column, for example. However, a simpler and faster procedure can optionally be used for some changes that do no affect the on-disk content in any way. The following simpler procedure is appropriate for removing CHECK or FOREIGN KEY or NOT NULL constraints, |
︙ | ︙ |
Changes to pages/pragma.in.
︙ | ︙ | |||
771 772 773 774 775 776 777 | } Pragma legacy_alter_table { <p>^(<b>PRAGMA legacy_alter_table; <br>PRAGMA legacy_alter_table = <i>boolean</i></b></p> <p>This pragma sets or queries the value of the legacy_alter_table | | | | | | | 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 | } Pragma legacy_alter_table { <p>^(<b>PRAGMA legacy_alter_table; <br>PRAGMA legacy_alter_table = <i>boolean</i></b></p> <p>This pragma sets or queries the value of the legacy_alter_table flag.)^ ^(When this flag is on, the [ALTER TABLE RENAME] command (for changing the name of a table) works as it did in SQLite 3.24.0 ([dateof:3.24.0]) and earlier.)^ More specifically, when this flag is on the [ALTER TABLE RENAME] command only rewrites the initial occurrence of the table name in its [CREATE TABLE] statement and in any associated [CREATE INDEX] and [CREATE TRIGGER] statements. Other references to the table are unmodifed, including: <ul> <li> References to the table within the bodies of triggers and views. <li> References to the table within CHECK constraints in the original CREATE TABLE statement. <li> References to the table within the WHERE clauses of [partial indexes]. </ul> The default setting for this pragma is OFF, which means that all references to the table anywhere in the schema are converted to the new name. <p>This pragma is provided as a work-around for older programs that 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. } |
︙ | ︙ |