Using SQLite

Name

ALTER TABLE — Modify an existing table

Syntax

Common Usage

ALTER TABLE database_name.table_name RENAME TO new_table_name;
ALTER TABLE database_name.table_name ADD COLUMN column_def...;

Description

The ALTER TABLE command modifies an existing table without performing a full dump and reload of the data. The SQLite version of ALTER TABLE supports two basic operations. The RENAME variant is used to change the name of a table, while ADD COLUMN is used to add a new column to an existing table. Both versions of the ALTER TABLE command will retain any existing data in the table.

RENAME

The RENAME variant is used to “move” or rename an existing table. An ALTER TABLE...RENAME command can only modify a table in place, it cannot be used to move a table to another database. A database name can be provided when specifying the original table name, but only the table name should be given when specifying the new table name.

Indexes and triggers associated with the table will remain with the table under the new name. If foreign key support is enabled, any foreign keys that reference this table will also be updated.

View definitions and trigger statements that reference the table by name will not be modified. These statements must be dropped and recreated, or a replacement table must be created.

ADD COLUMN

The ADD COLUMN variant is used to add a new column to the end of a table definition. New columns must ...