How to delete or add column in SQLITE?

Question

I want to delete or add column in sqlite database

I am using following query to delete column.

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

But it gives error

System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error

Answer 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.

Answer 2

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 
);

Answer 3

As an alternative:

If you have a table with schema

CREATE TABLE person(
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  height INTEGER
);

you can use a CREATE TABLE...AS statement like CREATE TABLE person2 AS SELECT id, first_name, last_name, age FROM person;, i.e. leave out the columns you don't want. Then drop the original person table and rename the new one.

Note this method produces a table has no PRIMARY KEY and no constraints. To preserve those, utilize the methods others described to create a new table, or use a temporary table as an intermediate.

Answer 4

You can use the SQlite Administrator for changing the column names. Right Click on Table name and select Edit Table.Here you will find the table structure and you can easily rename it.

Answer 5

example to add a column:-

alter table student add column TOB time;

here student is table_name and TOB is column_name to be added.

It is working and tested.

Answer 6

I have improved user2638929 answer and now it can preserves column type, primary key, default value etc.

private static void dropColumn(SupportSQLiteDatabase database, String tableName, List<String> columnsToRemove){
    List<String> columnNames = new ArrayList<>();
    List<String> columnNamesWithType = new ArrayList<>();
    List<String> primaryKeys = new ArrayList<>();
    String query = "pragma table_info(" + tableName + ");";
    Cursor cursor = database.query(query);
    while (cursor.moveToNext()){
        String columnName = cursor.getString(cursor.getColumnIndex("name"));

        if (columnsToRemove.contains(columnName)){
            continue;
        }

        String columnType = cursor.getString(cursor.getColumnIndex("type"));
        boolean isNotNull = cursor.getInt(cursor.getColumnIndex("notnull")) == 1;
        boolean isPk = cursor.getInt(cursor.getColumnIndex("pk")) == 1;

        columnNames.add(columnName);
        String tmp = "`" + columnName + "` " + columnType + " ";
        if (isNotNull){
            tmp += " NOT NULL ";
        }

        int defaultValueType = cursor.getType(cursor.getColumnIndex("dflt_value"));
        if (defaultValueType == Cursor.FIELD_TYPE_STRING){
            tmp += " DEFAULT " + "\"" + cursor.getString(cursor.getColumnIndex("dflt_value")) + "\" ";
        }else if(defaultValueType == Cursor.FIELD_TYPE_INTEGER){
            tmp += " DEFAULT " + cursor.getInt(cursor.getColumnIndex("dflt_value")) + " ";
        }else if (defaultValueType == Cursor.FIELD_TYPE_FLOAT){
            tmp += " DEFAULT " + cursor.getFloat(cursor.getColumnIndex("dflt_value")) + " ";
        }
        columnNamesWithType.add(tmp);
        if (isPk){
            primaryKeys.add("`" + columnName + "`");
        }
    }
    cursor.close();

    String columnNamesSeparated = TextUtils.join(", ", columnNames);
    if (primaryKeys.size() > 0){
        columnNamesWithType.add("PRIMARY KEY("+ TextUtils.join(", ", primaryKeys) +")");
    }
    String columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType);

    database.beginTransaction();
    try {
        database.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
        database.execSQL("CREATE TABLE " + tableName + " (" + columnNamesWithTypeSeparated + ");");
        database.execSQL("INSERT INTO " + tableName + " (" + columnNamesSeparated + ") SELECT "
                + columnNamesSeparated + " FROM " + tableName + "_old;");
        database.execSQL("DROP TABLE " + tableName + "_old;");
        database.setTransactionSuccessful();
    }finally {
        database.endTransaction();
    }
}

PS. I used here android.arch.persistence.db.SupportSQLiteDatabase, but you can easyly modify it for use android.database.sqlite.SQLiteDatabase