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

¹⁾

https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes