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: |
d4418abf42ce77750b6cde32202f3b05 |
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
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 | 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> | | | > > > | | < < > > < < > > | > | > > > > > | > > > | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > | | 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 |
︙ | ︙ |