I am wanting to drop a column from a table in my SQLite database, and it seems there is no support for dropping columns with ALTER TABLE. Currently, I am using CREATE TABLE AS to create a new temporary table (minus the column I don't want) from one of my existing tables. I planned on dropping the original table and renaming the temporary table, but I am running across some problems after making the temporary table.
Say I have a table, origTable, with columns RowID, A, and B. I am able to run this query with no problem:
SQLiteCommand cmd = new SQLiteCommand("CREATE TABLE tmpTable AS SELECT RowID,A FROM
origTable;", con);
tmpTable is created and column B is effectively dropped, however RowID is no longer defined as the primary key or as an auto increment value.
Is there any way I can retain the column properties when creating the temporary table? Do you have any other ideas on how to solve this problem?
Note: My ultimate goal is to have a table that users can add and remove data columns from. So I don't really know what my column list will be or which column the user will want to remove until runtime. Right now I am grabbing the table schema, getting all the column names, removing the column to be deleted, and using this string list to generate the temporary table.
Thanks in advance for any help, it is appreciated. -Steven