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 always go at the end of the table definition. The existing table rows are not actually modified, meaning that the added columns are implied until a row is modified. This means the ALTER TABLE...ADD COLUMN command is quite fast, even for large tables, but it also means there are some limitations on the columns that can be added.

The added column:

• Cannot have a PRIMARY KEY constraint

• Cannot have a UNIQUE constraint

• Must have a literal, non-NULL default value if a NOT NULL constraint is given

• Cannot have a default of CURRENT_TIME, CURRENT_DATE, or CURRENT_TIMESTAMP

If foreign key constraints are enabled and the added column is defined as a foreign key (it has a REFERENCES clause), the new column must have a default of NULL.

Additionally, if the new column has a CHECK constraint, that constraint will only be applied to new values. This can lead to data that is inconsistent with the CHECK.

There is no way to remove a column once it has been added.

See Also

CREATE TABLE