SQLite Forum

Well, there is actually a quite simply way of thinking about `pragma legacy_alter_table`.

The original (or `legacy_alter_table=1`) method did not assume or require that the database schema is internally consistent before making a change, and did not require it to be consistent after the change was made.  It simply processes `alter table` commands verbatim, with no attempt to maintain consistency or coherence or integrity of the database.

The new (or `legacy_alter_table=0`) method **assumes and (sometimes) requires** that the database schema is internally consistent **before** making a change; and **requires** that the database schema is internally consistent **after** having made the change.  It does this my making all the necessary changes to the schema to transform it from the **before the command is executed assumed consistent state** to the **after the command is executed consistent state**, such as renaming columns/indexes/constraints/views/triggers as necessary wherever they may be found (and if something is being missed that is a bug and should be reported so Richard can find and fix it).  If the database schema is inconsistent **before** the change or would be inconsistent **after** the change, then an error is thrown.  (though not all inconsistencies before the change are errors).

Note that this sometimes precludes being able to use a `legacy_alter_table=0` alter table command to change the database from an **inconsistent** state to a **consistent** one.  In such a case one must use `legacy_alter_table=1`.

Or for short, `legacy_alter_table=1` means **do as you are told, only what you are told, exactly as you are told**; whereas `legacy_alter_table=0` applies the transformation requested **on an assumed consistent schema** and results **in a consistent schema**, so its use on an inconsistent schema may be an error.