Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Update the documentation to reflect the enhancements to ALTER TABLE. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
c3c04a7bb4b30461f367c68bfdb29ab5 |
User & Date: | drh 2018-09-06 20:34:51.414 |
Context
2018-09-06
| ||
20:36 | Fix a long-standing typo in the ALTER TABLE documentation. (check-in: 9b5947f2ee user: drh tags: trunk) | |
20:34 | Update the documentation to reflect the enhancements to ALTER TABLE. (check-in: c3c04a7bb4 user: drh tags: trunk) | |
20:05 | Update the ALTER TABLE bubble diagram to show the RENAME COLUMN syntax. (check-in: f995368f57 user: drh tags: trunk) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
20 21 22 23 24 25 26 27 28 29 30 31 32 33 | set xrefChng($date) $nChng incr nChng } chng {2018-09-00 (3.25.0)} { <li> Add support for [window functions] <li> Added the [Geopoly module] <li> Query optimizer improvements: <ol type="a"> <li> Avoid unnecessary loads of columns in an aggregate query that are not within an aggregate function and that are not part of the GROUP BY clause. <li> The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column | > > > > > > > | 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | set xrefChng($date) $nChng incr nChng } chng {2018-09-00 (3.25.0)} { <li> Add support for [window functions] <li> Added the [Geopoly module] <li> Enhancements the [ALTER TABLE] command: <ol type="a"> <li> Add support for renaming columns within a table using ALTER TABLE <i>table</i> RENAME COLUMN <i>oldname</i> TO <i>newname</i>. <li> Fix table rename feature so that it also updates references to the renamed table in [CREATE TRIGGER|triggers] and [views]. </ol> <li> Query optimizer improvements: <ol type="a"> <li> Avoid unnecessary loads of columns in an aggregate query that are not within an aggregate function and that are not part of the GROUP BY clause. <li> The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
116 117 118 119 120 121 122 | ############################################################################### Section {ALTER TABLE} altertable {{ALTER TABLE} {*ALTER}} RecursiveBubbleDiagram alter-table-stmt </tcl> <p>SQLite supports a limited subset of ALTER TABLE. | | | < | < | | < < > | < < < < | > > | | | < < < | < < < < < < | 116 117 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 | ############################################################################### Section {ALTER TABLE} altertable {{ALTER TABLE} {*ALTER}} 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 attached to the table after it has been renamed. 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> The RENAME COLUMN TO syntax changes the <yyterm>old-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. <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:)^ |
︙ | ︙ | |||
175 176 177 178 179 180 181 | <p>^Note also that when adding a [CHECK constraint], the CHECK constraint is not tested against preexisting rows of the table. ^This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added.</p> | > > > > | | > | 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 | <p>^Note also that when adding a [CHECK constraint], the CHECK constraint is not tested against preexisting rows of the table. ^This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added.</p> <p>The ALTER TABLE command works by modifing the SQL text of the schema stored in the [sqlite_master table]. No changes are made to table content. Because of this, the execution time of the ALTER TABLE command is independent of 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</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> |
︙ | ︙ | |||
256 257 258 259 260 261 262 | 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, | | | 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 | 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, or adding, removing, or changing default values on a column. <ol> <lI><p> Start a transaction. <li><p> Run [PRAGMA schema_version] to determine the current schema version number. This number will be needed for step 6 below. |
︙ | ︙ |