How to delete or add column in SQLITE?

Solution 1:

ALTER TABLE SQLite

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

You can:

1. create new table as the one you are trying to change,
2. copy all data,
3. drop old table,
4. rename the new one.

Solution 2:

I've wrote a Java implementation based on the Sqlite's recommended way to do this:

private void dropColumn(SQLiteDatabase db,
        ConnectionSource connectionSource,
        String createTableCmd,
        String tableName,
        String[] colsToRemove) throws java.sql.SQLException {

    List<String> updatedTableColumns = getTableColumns(tableName);
    // Remove the columns we don't want anymore from the table's list of columns
    updatedTableColumns.removeAll(Arrays.asList(colsToRemove));

    String columnsSeperated = TextUtils.join(",", updatedTableColumns);

    db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");

    // Creating the table on its new format (no redundant columns)
    db.execSQL(createTableCmd);

    // Populating the table with the data
    db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
            + columnsSeperated + " FROM " + tableName + "_old;");
    db.execSQL("DROP TABLE " + tableName + "_old;");
}

To get the table's column, I used the "PRAGMA table_info":

public List<String> getTableColumns(String tableName) {
    ArrayList<String> columns = new ArrayList<String>();
    String cmd = "pragma table_info(" + tableName + ");";
    Cursor cur = getDB().rawQuery(cmd, null);

    while (cur.moveToNext()) {
        columns.add(cur.getString(cur.getColumnIndex("name")));
    }
    cur.close();

    return columns;
}

I actually wrote about it on my blog, you can see more explanations there:

http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/

Solution 3:

As others have pointed out

It is not possible to rename a column, remove a column, or add or remove constraints from a table.

source : http://www.sqlite.org/lang_altertable.html

While you can always create a new table and then drop the older one. I will try to explain this workaround with an example.

sqlite> .schema
CREATE TABLE person(
 id INTEGER PRIMARY KEY, 
 first_name TEXT,
 last_name TEXT, 
 age INTEGER, 
 height INTEGER
);
sqlite> select * from person ; 
id          first_name  last_name   age         height    
----------  ----------  ----------  ----------  ----------
0           john        doe         20          170       
1           foo         bar         25          171       

Now you want to remove the column height from this table.

Create another table called new_person

sqlite> CREATE TABLE new_person(
   ...>  id INTEGER PRIMARY KEY, 
   ...>  first_name TEXT, 
   ...>  last_name TEXT, 
   ...>  age INTEGER 
   ...> ) ; 
sqlite> 

Now copy the data from the old table

sqlite> INSERT INTO new_person
   ...> SELECT id, first_name, last_name, age FROM person ;
sqlite> select * from new_person ;
id          first_name  last_name   age       
----------  ----------  ----------  ----------
0           john        doe         20        
1           foo         bar         25        
sqlite>

Now Drop the person table and rename new_person to person

sqlite> DROP TABLE IF EXISTS person ; 
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>

So now if you do a .schema, you will see

sqlite>.schema
CREATE TABLE "person"(
 id INTEGER PRIMARY KEY, 
 first_name TEXT, 
 last_name TEXT, 
 age INTEGER 
);