How to alter a table structure with SQLite?

About

The alter statement of SQLite is pretty limited. This page shows you:

• what you can do and can't do with an alter table statement
• how to modify the structure of your table

What can you do?

You can:

• rename a table or a column
• add or drop a column

and that's it as you can see in this railroad diagram

What can't you do?

You can't modify

• a constraint (primary key, …)
• a type
• the order of column
• …

Solution

Full procedure

As you can't modify the structure via an alter statement, you need to:

• create the new table,
• move the data into the new table,
• delete the old table
• rename the new table.

The detailed procedure as stated in the documentation ¹⁾ is:

• Disable the foreign key check
• Start a transaction
• Create a new temporary table with the new structure
• Insert all data in it
• Delete the old table
• Rename the temporary table to the new one
• If necessary, create or recreate index, trigger, and views
• Check the constraints
• Close the transaction
• Enable foreign keys check

Simplified

For changes that do not affect the on-disk content such as:

• removing CHECK or FOREIGN KEY or NOT NULL constraints,
• or adding, removing, or changing default values on a column

the Sqlite documentation points to a simplified procedure.

Check the last paragraph of the alter section 7 documentation if you are interested

Example with primary key modification

An example where a name was taken as identifier and that a ID will take over.

• Disable foreign key

PRAGMA foreign_keys=OFF

• Start a transaction

BEGIN TRANSACTION;

• Create the new table with the target schema (via the Create statement)

create table THINGS_TMP
(
    ID INTEGER PRIMARY KEY,
    NAME TEXT UNIQUE,
    DESCRIPTION TEXT
);

• Copy all data into the temporary table

insert into THINGS_TMP (NAME, DESCRIPTION) from THINGS;

• Drop the old table and rename the temporary table

drop table THINGS;
alter table THINGS_TMP rename to THINGS;

• Create index, trigger, and view if needed
• Foreign Key check

PRAGMA foreign_key_check ;

• Commit (End the transaction)

Commit;

• Reenable foreign key

PRAGMA foreign_keys=ON