Documentation Source Text

Check-in [d4418abf42]
Login

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

Overview
Comment:Updates to the ALTER TABLE documentation. Provide a clear warning that RENAME TO does not rewrite referencing triggers and views. Provide two correct procedures for making other kinds of schema changes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d4418abf42ce77750b6cde32202f3b05ca95083b
User & Date: drh 2014-05-26 11:56:36.099
Context
2014-05-26
12:43
Better support for Windows in the howtocompile.html document. (check-in: 55ab2074b0 user: drh tags: trunk)
11:56
Updates to the ALTER TABLE documentation. Provide a clear warning that RENAME TO does not rewrite referencing triggers and views. Provide two correct procedures for making other kinds of schema changes. (check-in: d4418abf42 user: drh tags: trunk)
2014-05-25
22:11
Make sure fileformat.html stays in sync with fileformat2.html. (check-in: df6145bd4e user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
138
139
140
141
142
143
144











145
146
147
148
149
150
151
<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>












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







>
>
>
>
>
>
>
>
>
>
>







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
<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.
189
190
191
192
193
194
195
196
197


198
199

200
201
202
203
204
205


206
207
208
209


210
211
212
213
214

215





216



217


218




219
220
221
222
223
224
























































225

226
227
228
229
230
231
232
233
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>
Drop all indexes, triggers, and views associated with table X using 
appropriate [DROP INDEX], [DROP TRIGGER], and [DROP VIEW] commands.



<li><p>

Rename table X to some alternative name that does not collide with 
any other table in the schema.  
For example: ALTER TABLE X RENAME TO old_X.

<li><p>
Use the [CREATE TABLE] command to recreate table X in the new desired format.



<li><p>
Run an [INSERT | INSERT INTO X SELECT ... FROM old_X] command to transfer
content from the old version of X into the new X.



<li><p>
Drop the old X table using [DROP TABLE | DROP TABLE old_X].

<li><p>

Issue [CREATE INDEX], [CREATE TRIGGER], and [CREATE VIEW] to





recreate the indexes, triggers, and views that were dropped in step 1



above with changes appropriate for the


altered table definition.





</ol>

<p>If desired, all of the steps above can be carried out inside of a
[BEGIN | transaction] so that the schema change is atomic.

























































<p>If some future version of SQLite adds new ALTER TABLE capabilities, it

will probably do so using the steps outlined above.

<tcl>
#  One of the reasons that
# SQLite does not currently support more ALTER TABLE capabilities is that
# the procedure shown above is difficult to automate for an arbitrary schema.
# Particularly troublesome areas are identifying all views associated with
# table X in step 1 and creating new views and triggers that are compatible







|
|
>
>


>
|
|
<


<
>
>


<
<
>
>


|


>
|
>
>
>
>
>
|
>
>
>
|
>
>
|
>
>
>
>






>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
|







200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215

216
217

218
219
220
221


222
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
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
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
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>
Remember the format of all indexes and triggers associated with table X.
This information will be needed in step 7 below.  One way to do this is
to run a query like the following:
SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.

<li><p>
Use [CREATE TABLE] to construct a new table "new_X" that is in the desired
revised format of table X.  Make sure that the name "new_X" does not collide
with any existing table name, of course.


<li><p>

Transfer content from X into new_X using a statement
like: INSERT INTO new_X SELECT ... FROM X.

<li><p>


If foreign key constraints are enabled,
disable them using [PRAGMA foreign_keys | PRAGMA foreign_keys=OFF].

<li><p>
Drop the old table X:  [DROP TABLE | DROP TABLE X].

<li><p>
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

<li><p>
Use [CREATE INDEX] and [CREATE TRIGGER] to reconstruct indexes and triggers
associated with table X.  Perhaps use the old format of the triggers and
indexes saved from step 1 above as a guide, making changes as appropriate
for the alteration.

<li><p>
If foreign key constraints were originally turned on using (prior to
step 4) then run [PRAGMA foreign_key_check] to verify that the schema
change did not break any foreign key contraints, and run
[PRAGMA foreign_keys | PRAGMA foreign_keys=ON] to reenable foreign key
constraints.

<li><p>If any views refer to table X in a way that is affected by the
schema change, then drop those views using [DROP VIEW] and recreate them
with whatever changes are necessary to accomodate the schema change
using [CREATE VIEW].

</ol>

<p>If desired, all of the steps above can be carried out inside of a
[BEGIN | transaction] so that the schema change is atomic.

<p>The procedure above is completely general and will work even if the
schema change causes the information stored in the table to change.
So the full 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,
renaming columns, or adding or removing or changing default values on
a column.

<ol>
<li><p> Run [PRAGMA schema_version] to determine the current schema
version number.  This number will be needed for step 5 below.

<li><p> Active schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=ON].

<li><p> Run an [UPDATE] statement to change the definition of table X
in the [sqlite_master table]: 
UPDATE sqlite_master SET sql=... WHERE type='table' AND name='X';
<p><em>Caution:</em>  Making a change to the sqlite_master table like this will
render the database corrupt and unreadable if the change contains
a syntax error.  It is suggested that careful testing of the UPDATE
statement be done on a separate blank database prior to using it on
a database containing important data.

<li><p> If the change to table X also affects other tables or indexes or
triggers are views within schema, then run [UPDATE] statements to modify
those other tables indexes and views too.  For example, if the name of
a column changes, all FOREIGN KEY constraints, triggers, indexes, and
views that refer to that column must be modified.
<p><em>Caution:</em>  Once again, making changes to the sqlite_master 
table like this will render the database corrupt and unreadable if the 
change contains an error.  Carefully test of this entire procedure
on a separate test database prior to using it on
a database containing important data and/or make backup copies of
important databases prior to running this procedure.

<li><p> Increment the schema version number using
[PRAGMA schema_version | PRAGMA schema_version=X] where X is one
more than the old schema version number found in step 2 above.

<li><p> Disable schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=OFF].

<li><p> (Optional) Run [PRAGMA integrity_check] to verify that the
schema changes did not damage the database.
</ol>

<p>It is important that both of the above procedures be run from within
a transaction to prevent other processes from accessing the database file
while the schema change is only partially complete.  

<p>If some future version of SQLite adds new ALTER TABLE capabilities, 
those capabilities will very likely use one of the two procedures
outlined above.

<tcl>
#  One of the reasons that
# SQLite does not currently support more ALTER TABLE capabilities is that
# the procedure shown above is difficult to automate for an arbitrary schema.
# Particularly troublesome areas are identifying all views associated with
# table X in step 1 and creating new views and triggers that are compatible