Documentation Source Text

Check-in [c3c04a7bb4]
Login

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

Overview
Comment:Update the documentation to reflect the enhancements to ALTER TABLE.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:c3c04a7bb4b30461f367c68bfdb29ab51ed35b2fb6fd921d6603307933adff3e
User & Date: drh 2018-09-06 20:34:51
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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
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
...
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
201
...
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
###############################################################################
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
or to add a new column to an existing table.
</p>

<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.</p>

<p> ^If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.  ^However, if there are

any view definitions, or statements executed by triggers that refer to
the table being renamed, these are not automatically modified to use the new
table name. If this is required, the triggers or view definitions must be
dropped and recreated to use the new table name by hand.
</p>




<blockquote><table border="1" cellpadding="10">
<tr><td>
<em>Important Note:</em>
The 'ALTER TABLE ... RENAME TO ...' command does not update action
statements within triggers or SELECT statements within views.
If the table being renamed is referenced from within triggers or views,
then those triggers and views must be dropped and recreated separately
by the application.
</td></tr></table>
</blockquote>

<p>^If [foreign key constraints] are 
[foreign_keys pragma | enabled] when a table is renamed, then any
[foreign-key-clause | REFERENCES clauses] in any table (either the
table being renamed or some other table)
that refer to the table being renamed are modified to refer 
to the renamed table by its new name.

<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:)^
................................................................................

<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 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" 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>
................................................................................
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,
renaming columns, or adding or 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.







|
|
<





|
<
|
|
>
|
<
<
<


>
>
>
|
|
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<







 







>
>
>
>
|











|
>







 







|







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
...
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
...
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
###############################################################################
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:)^
................................................................................

<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>
................................................................................
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.