Documentation Source Text

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



























































<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

RecursiveBubbleDiagram analyze-stmt
</tcl>

<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 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>
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>
Recreate all the indexes, triggers, and views that were dropped in step 1
above and that remain relevant after the table alteration
using approprate [CREATE INDEX], [CREATE TRIGGER], and [CREATE VIEW]
commands.
</ol>

<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
# with the altered schema for table X in step 6.  It is a tricky but solvable
# problem to create code that will perform the steps above 
# for "reasonable" schemas commonly found in practice.  
# But there exist malevolent schemas for which
# these steps are maddeningly difficult to do correctly.  Rather than create
# ALTER TABLE features that work correctly 99.9% of the time but possibly
# corrupt the schema on the other 0.1%, the SQLite developers have decided
# to push the problem into the application domain, where it is much easier
# to solve.
</tcl>


<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

RecursiveBubbleDiagram analyze-stmt
</tcl>