The general strategy is to create a new table, insert data from the old table, drop the old table, rename the new table, and re-create the indexes:

  create table foo2 (
   col1 int,
   col2 text
  );
  insert into foo2 sleect col1, col2 from foo;
  drop table foo;
  alter table foo2 rename to foo;
  create index on foo(col1);

See: https://www.sqlite.org/lang_altertable.html#making_other_kin...

As for the reason, see the next section on that link. It's not perfect, but it is what it is. SQLite wasn't designed to solve 100% of the use cases, and that's not a bad thing.