Sqlite drop column error

1. Overview

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be renamed; a column can be added to it; or a column can be dropped from it.

2. ALTER TABLE RENAME

The RENAME TO syntax changes the name of table-name to new-table-name . 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.

Compatibility Note: The behavior of ALTER TABLE when renaming a table was enhanced in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01) in order to carry the rename operation forward into triggers and views that reference the renamed table. This is considered an improvement. Applications that depend on the older (and arguably buggy) behavior can use the PRAGMA legacy_alter_table=ON statement or the SQLITE_DBCONFIG_LEGACY_ALTER_TABLE configuration parameter on sqlite3_db_config() interface to make ALTER TABLE RENAME behave as it did prior to version 3.25.0.

Beginning with release 3.25.0 (2018-09-15), references to the table within trigger bodies and view definitions are also renamed.

Prior to version 3.26.0 (2018-12-01), FOREIGN KEY references to a table that is renamed were only edited if the PRAGMA foreign_keys=ON, or in other words if foreign key constraints were being enforced. With PRAGMA foreign_keys=OFF, FOREIGN KEY constraints would not be changed when the table that the foreign key referred to (the «parent table») was renamed. Beginning with version 3.26.0, FOREIGN KEY constraints are always converted when a table is renamed, unless the PRAGMA legacy_alter_table=ON setting is engaged. The following table summarizes the difference:

PRAGMA foreign_keys	PRAGMA legacy_alter_table	Parent Table references are updated	SQLite version
Off	Off	No	= 3.26.0
On	Off	Yes	all
Off	On	No	all
On	On	Yes	all

3. ALTER TABLE RENAME COLUMN

The RENAME COLUMN TO syntax changes the column-name of table table-name into new-column-name . 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.

4. ALTER TABLE ADD COLUMN

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:

• The column may not have a PRIMARY KEY or UNIQUE constraint.
• The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
• If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
• If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.
• The column may not be GENERATED ALWAYS . STORED, though VIRTUAL columns are allowed.

When adding a column with a CHECK constraint, or a NOT NULL constraint on a generated column, the added constraints are tested against all preexisting rows in the table and the ADD COLUMN fails if any constraint fails. The testing of added constraints against preexisting rows is a new enhancement as of SQLite version 3.37.0 (2021-11-27).

The ALTER TABLE command works by modifying the SQL text of the schema stored in the sqlite_schema table. No changes are made to table content for renames or column addition without constraints. Because of this, the execution time of such ALTER TABLE commands is independent of the amount of data in the table and such commands will run as quickly on a table with 10 million rows as on a table with 1 row. When adding new columns that have CHECK constraints, or adding generated columns with NOT NULL constraints, or when deleting columns, then all existing data in the table must be either read (to test new constraints against existing rows) or written (to remove deleted columns). In those cases, the ALTER TABLE command takes time that is proportional to the amount of content in the table being altered.

After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 (2005-02-20) and earlier.

5. ALTER TABLE DROP COLUMN

The DROP COLUMN syntax is used to remove an existing column from a table. The DROP COLUMN command removes the named column from the table, and rewrites its content to purge the data associated with that column. The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint. Possible reasons why the DROP COLUMN command can fail include:

• The column is a PRIMARY KEY or part of one.
• The column has a UNIQUE constraint.
• The column is indexed.
• The column is named in the WHERE clause of a partial index.
• The column is named in a table or column CHECK constraint not associated with the column being dropped.
• The column is used in a foreign key constraint.
• The column is used in the expression of a generated column.
• The column appears in a trigger or view.

5.1. How It Works

SQLite stores the schema as plain text in the sqlite_schema table. The DROP COLUMN command (and all of the other variations of ALTER TABLE as well) modify that text and then attempt to reparse the entire schema. The command is only successful if the schema is still valid after the text has been modified. In the case of the DROP COLUMN command, the only text modified is that the column definition is removed from the CREATE TABLE statement. The DROP COLUMN command will fail if there are any traces of the column in other parts of the schema that will prevent the schema from parsing after the CREATE TABLE statement has been modified.

6. Disable Error Checking Using PRAGMA writable_schema=ON

ALTER TABLE will normally fail and make no changes if it encounters any entries in the sqlite_schema table that do not parse. For example, if there is a malformed VIEW or TRIGGER associated with table named «tbl1», then an attempt to rename «tbl1» to «tbl1neo» will fail because the associated views and triggers could not be parsed.

Beginning with SQLite 3.38.0 (2022-02-22), this error checking can be disabled by setting «PRAGMA writable_schema=ON;». When the schema is writable, ALTER TABLE silently ignores any rows of the sqlite_schema table that do not parse.

7. Making Other Kinds Of Table Schema Changes

The only schema altering commands directly supported by SQLite are the «rename table», «rename column», «add column», «drop 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:

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

Start a transaction.

Remember the format of all indexes, triggers, and views associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_schema WHERE tbl_name=’X’.

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.

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

Drop the old table X: DROP TABLE X.

Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

Use CREATE INDEX, CREATE TRIGGER, and CREATE VIEW to reconstruct indexes, triggers, and views associated with table X. Perhaps use the old format of the triggers, indexes, and views saved from step 3 above as a guide, making changes as appropriate for the alteration.

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 accommodate the schema change using CREATE VIEW.

If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.

Commit the transaction started in step 2.

If foreign keys constraints were originally enabled, reenable them now.

Caution: Take care to follow the procedure above precisely. The boxes below summarize two procedures for modifying a table definition. At first glance, they both appear to accomplish the same thing. However, the procedure on the right does not always work, especially with the enhanced rename table capabilities added by versions 3.25.0 and 3.26.0. In the procedure on the right, the initial rename of the table to a temporary name might corrupt references to that table in triggers, views, and foreign key constraints. The safe procedure on the left constructs the revised table definition using a new temporary name, then renames the table into its final name, which does not break links.

The 12-step generalized ALTER TABLE procedure above will work even if the schema change causes the information stored in the table to change. So the full 12-step 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, or adding, removing, or changing default values on a column.

Start a transaction.

Run PRAGMA schema_version to determine the current schema version number. This number will be needed for step 6 below.

Activate schema editing using PRAGMA writable_schema=ON.

Run an UPDATE statement to change the definition of table X in the sqlite_schema table: UPDATE sqlite_schema SET sql=. WHERE type=’table’ AND name=’X’;

Caution: Making a change to the sqlite_schema 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.

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.

Caution: Once again, making changes to the sqlite_schema table like this will render the database corrupt and unreadable if the change contains an error. Carefully test 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.

Increment the schema version number using PRAGMA schema_version=X where X is one more than the old schema version number found in step 2 above.

(Optional) Run PRAGMA integrity_check to verify that the schema changes did not damage the database.

Commit the transaction started on step 1 above.

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

8. Why ALTER TABLE is such a problem for SQLite

Most SQL database engines store the schema already parsed into various system tables. On those database engines, ALTER TABLE merely has to make modifications to the corresponding system tables.

SQLite is different in that it stores the schema in the sqlite_schema table as the original text of the CREATE statements that define the schema. Hence ALTER TABLE needs to revise the text of the CREATE statement. Doing so can be tricky for certain «creative» schema designs.

The SQLite approach of storing the schema as text has advantages for an embedded relational database. For one, it means that the schema takes up less space in the database file. This is important since a common SQLite usage pattern is to have many small, separate database files instead of putting everything in one big global database file, which is the usual approach for client/server database engines. Since the schema is duplicated in each separate database file, it is important to keep the schema representation compact.

Storing the schema as text rather than as parsed tables also give flexibility to the implementation. Since the internal parse of the schema is regenerated each time the database is opened, the internal representation of the schema can change from one release to the next. This is important, as sometimes new features require enhancements to the internal schema representation. Changing the internal schema representation would be much more difficult if the schema representation was exposed in the database file. So, in other words, storing the schema as text helps maintain backwards compatibility, and helps ensure that older database files can be read and written by newer versions of SQLite.

Storing the schema as text also makes the SQLite database file format easier to define, document, and understand. This helps make SQLite database files a recommended storage format for long-term archiving of data.

The downside of storing schema a text is that it can make the schema tricky to modify. And for that reason, the ALTER TABLE support in SQLite has traditionally lagged behind other SQL database engines that store their schemas as parsed system tables that are easier to modify.

Источник

When you do not know the recipe blog post style take a look at the introduction, or check out all recipes there are.

As usual in the recipe section we will look at the following:

• The problem to solve: Sqlite3 and ef core with alter table migrations
• Why would you do this: Sqlite3 has a limited set of alter table statements
• What do we actually do with this: find a way to circumvent this and still use the migrations feature
• How are we going to do it: Change the modelbuilder migrations file manually
• Caveats: changing the file comes with some risk and puts more error and work on your side

In this post we will see that ef core has some specifics when it comes to a given database provider.
In this case with sqlite3. Because we are going to drop a column due to the reason that we remove a property on an entity, we will need to supply a workaround, because sqlite3 does not allow for Alter table statements.

The problem to solve

Fix Sqlite3 and ef core Drop Column statement with migrations.
When using ef core migrations to keep the database up with your changes in business entities sometimes requires to remove properties from an entity.

This is usally no problem, but in the case of sqlite3 and the provider the migrations will throw an error, because the created drop column statement is not supported by sqlite.

Why

Software is not carved into stone and requirements change while you learn more about the domain you are operating in.
This naturally leads to the change of the applications business entities. To have this incorporated in the database we use migrations in ef core.

But as mentioned in the intro, sqlite and migrations do not work in all cases of updates on the entities.
Of course we want to fix this, so we can still use sqlite3 and ef core migrations to have all the benefits that come with ef-core.

Also with this example we can show that entity framework core fails for a given provider and what we can do about it.
In this post we will see how this is implemente from a technical standpoint with Sqlite3

What

Create a situation where we have a migration that fails us when using sqlite3. Then fix it by adjusting the generated files manually.

We will use the example application that is provided on the github to this blog

First we will update an entity by adding two properties, and an attribute that puts a defaultValue on a property.
With this in place we run a migration on the application. With this migration we will then update the database, seed it and show the results.

How

In the example app add a Property to the TodoItem and remove the Date property.

public class TodoEntity
{
public int Id { get; set; }
public string Content { get; set; }
public DateTime? CompletedAtDate {get;set; }
public DateTime CreatedAtDate { get;set;}
public bool IsDone { get; set; }
}

We now have a creation date and a completion date (which is nullable because a todo item can still be opened)

After this we create the migration:

dotnet ef migrations add AddedCreationAndCompletionTimeToTodoItem 
-c TodoItemContext -o ./Data/Migrations

From the root of our project.
The resulting Up method looks like this:

protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(
name: "Date",
table: "TodoItems");
migrationBuilder.AddColumn<DateTime>(
name: "CompletedAtDate",
table: "TodoItems",
nullable: true);
migrationBuilder.AddColumn<DateTime>(
name: "CreatedAtDate",
table: "TodoItems",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
}

We can see how the old property results in a drop column and the other two properties result in an add column migrationbuilder expression.

The next thing to do is to run this command from the root of the project.

dotnet ef database update

This results in the following error:

Because we use sqlite3 in this case we cannot use the DROP COLUMN instruction.
Generally this does work for migrations, but for sqlite we need to make it work by a workaround that we examine in the next steps of this post.

We could simply change it directly on the database by hand or by running a script, but this would violate the single point of truth principle that ef core helps to achieve.

Workaround to ensure single point of truth

Sqlite simply does not support all the alter table statements which is why you need to change the table in the following steps:

1. Create a new table named as the one you are trying to change
2. Copy all the data to the new table
3. Drop the old table
4. Rename the new table with the old name

This can be done with editing the migration .cs file (not the designer).
We need to change the migration file like this:

{
migrationBuilder.CreateTable(
name: "New_TodoItems",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("Sqlite:Autoincrement", true),
Content = table.Column<string>(nullable: true),
CreatedAtDate = table.Column<DateTime?>(nullable:true),
CompletedAtDate = table.Column<DateTime?>(nullable: true),
IsDone = table.Column<bool>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_TodoItems", x => x.Id);
});
migrationBuilder.Sql("INSERT INTO New_TodoItems SELECT Id FROM TodoItems;");
migrationBuilder.Sql("PRAGMA foreign_keys="0"", true);
migrationBuilder.Sql("DROP TABLE TodoItems", true);
migrationBuilder.Sql("ALTER TABLE New_TodoItems RENAME TO TodoItems", true);
migrationBuilder.Sql("PRAGMA foreign_keys="1"", true);
// omitted the AddColumn Statements
}

This exactly reproduces the four steps mentioned above by using the migrationBuilder.Sql method that allows to specify the sql statements by yourself.
Also it sets the PRAGMA keys. Why this is needed see here.

When we now run the command

dotnet ef database update

Everything works fine and we are good to go.

See here for more details on why this does not work.

Caveats

• With manually editing the generated files you have two issues:
  • each migration needs some manual work
  • is error prone
  • makes the files tightly coupled to the sqlite-provider
    • so make sure to have two files (one original and one with the sqlite code)

If you’ve done any work with SQLite databases you surely know that this database is very limited in terms of making changes to the database schema. When working with a migration framework such as Flask-Migrate, it is common to end up with migration scripts that fail to upgrade or downgrade just because they need to remove or modify a column in a table, something that SQLite does not support.

In this article I’m going to discuss this limitation of the SQLite database, and show you a workaround that is specific to Flask-Migrate and Alembic.

SQLite’s ALTER TABLE Implementation

Changes that you make to the fields in your model or to the constraints associated with them will end up as an ALTER TABLE statement sent to the database. If you are using MySQL, Postgres or most other database servers besides SQLite, this isn’t a problem. With SQLite, however, the ALTER TABLE command only supports adding or renaming columns. Any other change to columns or constraints is going to be rejected with an error.

You can test this yourself very easily. Take any Flask-SQLAlchemy application (you can use one of mine) and after making sure your database is up to date, remove or comment out a column in one of the models. Then generate a migration:

(venv) $ flask db migrate -m "remove a column"

If you open the generated migration script everything will look correct. Below you can see the migration that was generated after I removed a column named about_me from the User model:

"""remove a column

Revision ID: ec813e760b53
Revises: 834b1a697901
Create Date: 2020-07-19 18:18:44.066766

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'ec813e760b53'
down_revision = '834b1a697901'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('user', 'about_me')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('about_me', sa.VARCHAR(length=140), nullable=True))
    # ### end Alembic commands ###

The problem occurs when you try to upgrade the database with this migration:

(venv) $ flask db upgrade
[2020-07-19 18:21:14,268] INFO in __init__: Microblog startup
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 834b1a697901 -> ec813e760b53, remove a column
Traceback (most recent call last):
  File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "DROP": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
[ ... removed a long list of uninteresting stack frames ... ]
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error
[SQL: ALTER TABLE user DROP COLUMN about_me]
(Background on this error at: http://sqlalche.me/e/e3q8)

If you tried this on your database, delete the broken migration script before continuing. You will learn how to generate migrations that work better in the next section.

Using Batch Mode

Unfortunately there is no simple way to perform these operations that SQLite has not implemented. The only way to do this is to generate a brand new table with the new schema and copy all the data. Alembic includes support for migrating a table in this way with a feature called «batch mode».

You can enable batch mode in Flask-Migrate right when you initialize the extension. If you use the direct method of initialization:

migrate = Migrate(app, db, render_as_batch=True)

If you use the two-step initialization:

migrate = Migrate()

def create_app():
    # ...
    migrate.init_app(app, db, render_as_batch=True)
    # ...

Now that you have batch mode enabled, try to generate the migration again:

(venv) $ flask db migrate -m "remove a column"

Nothing appears to be different, but if you look at the new migration script, you will see differences:

"""remove a column

Revision ID: adfac8a1b2ee
Revises: 834b1a697901
Create Date: 2020-07-19 18:32:27.782197

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'adfac8a1b2ee'
down_revision = '834b1a697901'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.drop_column('about_me')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.add_column(sa.Column('about_me', sa.VARCHAR(length=140), nullable=True))

    # ### end Alembic commands ###

Complications

There are situations in which batch mode alone does not solve upgrade errors.

A nasty type of issue occurs when the ALTER TABLE error occurs in the middle of a migration, after some operations were already applied. This could leave your database in an inconsistent state, where some changes from the migration script have been applied, but because of the error the version information is still pointing to the previous migration.

To unblock a database after a partial migration was applied I follow these steps:

• Determine which of the operations were applied.
• Delete everything from the upgrade() function.
• Edit the downgrade() function so that it only contains the reverse of the operations that were applied to your database.
• Run flask db upgrade. This is going to succeed because now you are running an empty upgrade. The database version will be upgraded.
• Run flask db downgrade. This will undo those partial changes that were applied earlier, and reset the database version back to the last good state.
• Delete the migration script and try again with batch mode enabled.

Another common issue occurs when your table has unnamed constraints, which the batch mode process can’t delete or modify because there is no way to refer to them by name. The Alembic documentation has some information on how to deal with unnamed constraints when using batch mode.

Conclusion

I hope this was a useful tip that can help you improve your migration workflow. Have you had issues with SQLite migrations that are not covered in this article? Let me know below in the comments.

1. Overview

alter-table-stmt:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be renamed; a column can be added to it; or a column can be dropped from it.

2. ALTER TABLE RENAME

The RENAME TO syntax changes the name of table-name to new-table-name. 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.

 Compatibility Note: The behavior of ALTER TABLE when renaming a table was enhanced in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01) in order to carry the rename operation forward into triggers and views that reference the renamed table. This is considered an improvement. Applications that depend on the older (and arguably buggy) behavior can use the PRAGMA legacy_alter_table=ON statement or the SQLITE_DBCONFIG_LEGACY_ALTER_TABLE configuration parameter on sqlite3_db_config() interface to make ALTER TABLE RENAME behave as it did prior to version 3.25.0. 

Beginning with release 3.25.0 (2018-09-15), references to the table within trigger bodies and view definitions are also renamed.

Prior to version 3.26.0 (2018-12-01), FOREIGN KEY references to a table that is renamed were only edited if the PRAGMA foreign_keys=ON, or in other words if foreign key constraints were being enforced. With PRAGMA foreign_keys=OFF, FOREIGN KEY constraints would not be changed when the table that the foreign key referred to (the «parent table») was renamed. Beginning with version 3.26.0, FOREIGN KEY constraints are always converted when a table is renamed, unless the PRAGMA legacy_alter_table=ON setting is engaged. The following table summarizes the difference:

PRAGMA foreign_keys	PRAGMA legacy_alter_table	Parent Table references are updated	SQLite version
Off	Off	No	< 3.26.0
Off	Off	Yes	>= 3.26.0
On	Off	Yes	all
Off	On	No	all
On	On	Yes	all

3. ALTER TABLE RENAME COLUMN

The RENAME COLUMN TO syntax changes the column-name of table table-name into new-column-name. 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.

4. ALTER TABLE ADD COLUMN

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:

• The column may not have a PRIMARY KEY or UNIQUE constraint.
• The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
• If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
• If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.
• The column may not be GENERATED ALWAYS … STORED, though VIRTUAL columns are allowed.

When adding a column with a CHECK constraint, or a NOT NULL constraint on a generated column, the added constraints are tested against all preexisting rows in the table and the ADD COLUMN fails if any constraint fails. The testing of added constraints against preexisting rows is a new enhancement as of SQLite version 3.37.0 (2021-11-27).

The ALTER TABLE command works by modifying the SQL text of the schema stored in the sqlite_schema table. No changes are made to table content for renames or column addition without constraints. Because of this, the execution time of such ALTER TABLE commands is independent of the amount of data in the table and such commands will run as quickly on a table with 10 million rows as on a table with 1 row. When adding new columns that have CHECK constraints, or adding generated columns with NOT NULL constraints, or when deleting columns, then all existing data in the table must be either read (to test new constraints against existing rows) or written (to remove deleted columns). In those cases, the ALTER TABLE command takes time that is proportional to the amount of content in the table being altered.

After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 (2005-02-20) and earlier.

5. ALTER TABLE DROP COLUMN

The DROP COLUMN syntax is used to remove an existing column from a table. The DROP COLUMN command removes the named column from the table, and rewrites its content to purge the data associated with that column. The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint. Possible reasons why the DROP COLUMN command can fail include:

• The column is a PRIMARY KEY or part of one.
• The column has a UNIQUE constraint.
• The column is indexed.
• The column is named in the WHERE clause of a partial index.
• The column is named in a table or column CHECK constraint not associated with the column being dropped.
• The column is used in a foreign key constraint.
• The column is used in the expression of a generated column.
• The column appears in a trigger or view.

5.1. How It Works

SQLite stores the schema as plain text in the sqlite_schema table. The DROP COLUMN command (and all of the other variations of ALTER TABLE as well) modify that text and then attempt to reparse the entire schema. The command is only successful if the schema is still valid after the text has been modified. In the case of the DROP COLUMN command, the only text modified is that the column definition is removed from the CREATE TABLE statement. The DROP COLUMN command will fail if there are any traces of the column in other parts of the schema that will prevent the schema from parsing after the CREATE TABLE statement has been modified.

6. Disable Error Checking Using PRAGMA writable_schema=ON

ALTER TABLE will normally fail and make no changes if it encounters any entries in the sqlite_schema table that do not parse. For example, if there is a malformed VIEW or TRIGGER associated some some table named «tbl1», then an attempt to rename «tbl1» to «tbl1neo» will fail because the associated views and triggers could not be parsed.

Beginning with SQLite 3.38.0 (2022-02-22), this error checking can be disabled by setting «PRAGMA writable_schema=ON;». When the schema is writable, ALTER TABLE silently ignores any rows of the sqlite_schema table that do not parse.

7. Making Other Kinds Of Table Schema Changes

The only schema altering commands directly supported by SQLite are the «rename table», «rename column», «add column», «drop 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:

1. If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.

2. Start a transaction.

3. Remember the format of all indexes, triggers, and views associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_schema WHERE tbl_name=’X’.

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

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

6. Drop the old table X: DROP TABLE X.

7. Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

8. Use CREATE INDEX, CREATE TRIGGER, and CREATE VIEW to reconstruct indexes, triggers, and views associated with table X. Perhaps use the old format of the triggers, indexes, and views saved from step 3 above as a guide, making changes as appropriate for the alteration.

9. 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 accommodate the schema change using CREATE VIEW.

10. If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.

11. Commit the transaction started in step 2.

12. If foreign keys constraints were originally enabled, reenable them now.

Caution: Take care to follow the procedure above precisely. The boxes below summarize two procedures for modifying a table definition. At first glance, they both appear to accomplish the same thing. However, the procedure on the right does not always work, especially with the enhanced rename table capabilities added by versions 3.25.0 and 3.26.0. In the procedure on the right, the initial rename of the table to a temporary name might corrupt references to that table in triggers, views, and foreign key constraints. The safe procedure on the left constructs the revised table definition using a new temporary name, then renames the table into its final name, which does not break links.

The 12-step generalized ALTER TABLE procedure above will work even if the schema change causes the information stored in the table to change. So the full 12-step 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, or adding, removing, or changing default values on a column.

1. Start a transaction.

2. Run PRAGMA schema_version to determine the current schema version number. This number will be needed for step 6 below.

3. Activate schema editing using PRAGMA writable_schema=ON.

4. Run an UPDATE statement to change the definition of table X in the sqlite_schema table: UPDATE sqlite_schema SET sql=… WHERE type=’table’ AND name=’X’;

   Caution: Making a change to the sqlite_schema 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.

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

   Caution: Once again, making changes to the sqlite_schema table like this will render the database corrupt and unreadable if the change contains an error. Carefully test 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.

6. Increment the schema version number using PRAGMA schema_version=X where X is one more than the old schema version number found in step 2 above.

7. Disable schema editing using PRAGMA writable_schema=OFF.

8. (Optional) Run PRAGMA integrity_check to verify that the schema changes did not damage the database.

9. Commit the transaction started on step 1 above.

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

8. Why ALTER TABLE is such a problem for SQLite

Most SQL database engines store the schema already parsed into various system tables. On those database engines, ALTER TABLE merely has to make modifications to the corresponding system tables.

SQLite is different in that it stores the schema in the sqlite_schema table as the original text of the CREATE statements that define the schema. Hence ALTER TABLE needs to revise the text of the CREATE statement. Doing so can be tricky for certain «creative» schema designs.

The SQLite approach of storing the schema as text has advantages for an embedded relational database. For one, it means that the schema takes up less space in the database file. This is important since a common SQLite usage pattern is to have many small, separate database files instead of putting everything in one big global database file, which is the usual approach for client/server database engines. Since the schema is duplicated in each separate database file, it is important to keep the schema representation compact.

Storing the schema as text rather than as parsed tables also give flexibility to the implementation. Since the internal parse of the schema is regenerated each time the database is opened, the internal representation of the schema can change from one release to the next. This is important, as sometimes new features require enhancements to the internal schema representation. Changing the internal schema representation would be much more difficult if the schema representation was exposed in the database file. So, in other words, storing the schema as text helps maintain backwards compatibility, and helps ensure that older database files can be read and written by newer versions of SQLite.

Storing the schema as text also makes the SQLite database file format easier to define, document, and understand. This helps make SQLite database files a recommended storage format for long-term archiving of data.

The downside of storing schema a text is that it can make the schema tricky to modify. And for that reason, the ALTER TABLE support in SQLite has traditionally lagged behind other SQL database engines that store their schemas as parsed system tables that are easier to modify.

SQLite

3.40

• SQL As Understood By SQLite

  SQLite understands most of the standard language.

• Built-in Aggregate Functions

  aggregate-function-invocation: hide expr: show literal-value: show over-clause: show frame-spec: show ordering-term: show raise-function: show select-stmt:

• ANALYZE

  analyze-stmt: hide The ANALYZE command gathers statistics about tables indices stores collected information internal of database where query optimizer

• ATTACH DATABASE

  attach-stmt: hide expr: show filter-clause: show literal-value: show over-clause: show frame-spec: show ordering-term: show raise-function: show select-stmt: