I've been reading up a bit and I understand SQLite has limited support for ALTER TABLE columns can be added but not dropped.
Some suggestions are to create a temp table and copy the data to it drop the old table create the new one and then select the data back. But there appears to be more to this. What I decided to do so far is: Lets say the table I am working with is TEMP1 BEGIN; CREATE TEMP TABLE TEMP1_TEMP as SELECT * FROM TEMP1; DROP TEMP1; CREATE TEMP1 ( NEW FIELDS); Now at this point select all the fields that still exist after the alteration INSERT INTO TEMP1 (FIELD1, FIELD2) SELECT FIELD1, FIELD2 from TEMP1_TEMP But the problem is it appears that sqlite automatically drops associated triggers and indexes. You can probably query the index schema by doing a select from sqlite_master When the indexes are determined do a PRAGMA index_info(idx_temp1..) for instance to get the associated fields. If those still exist, recreate the index using the same schema. Maybe the same can be done with the trigger. If the table has a sequence then it appears this is taken care of automatically in the select? Any foreign key constraints or references to the table in other triggers should be ok as long as you don't delete any primary key columns? As the select back and forth from the temp keeps the values of the primary keys as long as they remain? Im trying to figure out if I am missing something, or maybe exactly what others have done. Thanks in advance.
