Change type of column

Question

I have a table like this:

 private void Crea_TabellaIconePers(SQLiteDatabase db) {
    String sqlFoto = "CREATE TABLE IF NOT EXISTS {0} " +
            "({1} INTEGER, " +
            "{2} BLOB, " +
            "{3} TEXT);";
    db.execSQL(MessageFormat.format(sqlFoto,
            Tab_Icone_pers.TABLE_NAME,
            Tab_Icone_pers._ID,
            Tab_Icone_pers.ICONA,
            Tab_Icone_pers.ID_PASSWORD));
}

I want to convert the first INTEGER field to INTEGER PRIMARY KEY AUTOINCREMENT can it be done with the onUpgrade () method? I have read some documentation about, but have not found anything that talks about this.

score 0 · Answer 1 · answered Nov 20 '17 at 21:30

0

SQLite doesn't support such kind of table altering. You will need to create another table with the desired schema and copy all values from the original table there as needed.

Answering your question - yes, it can be done within onUpgrade() method.

answered Nov 20 '17 at 21:30

The Dreams Wind

8,416
2
19
49

score 0 · Answer 2 · answered Nov 20 '17 at 22:02

This is how I do it:

First, alter up your sql that makes the table to match what you want it to look like then do something similar to this:

String cols = COL_NAME_1 + ", " +
                    COL_NAME_2 + ", " +
                    COL_NAME_3 + ", " +
                    COL_NAME_4 + ", " +
                    COL_NAME_5 + ", " +
                    COL_NAME_6 ;
            db.execSQL("ALTER TABLE " + TABLE_1 + " RENAME TO " + TABLE_1 + "_old;");
            db.execSQL(CREATE_TABLE_1);
            db.execSQL("INSERT INTO " + TABLE_1 + "(" + cols + ") SELECT " + cols + " FROM " + TABLE_1 +"_old;");
            db.execSQL("DROP TABLE " + TABLE_1 + "_old;");

So we are making a copy of the table, remaking the table with any modifications, copying the data from the old table to the new one then dropping the old table.

You can't use "if exists" in this case, you have to use "create table table_1..."

Hopefully this is what you were after.

Change type of column

2 Answers2