CODEWITHSUNDEEP
androidsqlite
Riyas2329
Riyas2329

Reputation: 43

Add new column into database table

How do I add new columns into a table? I have a table called Subject and a column called module. Now I would like to add new columns called question and answer into the table Subject. Example:

Table: Subject

Module: Maths

Question: 2x2

Answer: 4

DBHelper:

public class DBHelper extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "Voting";

// Set table name
static final String TABLE_NAME = "Subject";

// Set Table Columns
private static final String KEY_ID = "id";
static final String KEY_MODULE = "module";
static final String KEY_QUESTION = "question";
static final String KEY_ANSWER = "answer";

public DBHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    // Category table create query
    String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_NAME + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_MODULE + " TEXT" + KEY_QUESTION + "TEXT" + KEY_ANSWER + "TEXT)";
    db.execSQL(CREATE_CATEGORIES_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);

    // Create tables again
    onCreate(db);
}

/**
 * Inserting new Column into subjects table
 * */
public void insertColumn(String column){
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_MODULE, column);

    // Inserting Row
    db.insert(TABLE_NAME, null, values);
    db.close(); // Closing database connection
}

It will be helpful if my code is updated in the answers so that I can understand easily.

Upvotes: 1

Views: 2347

Answers (3)

Abdur Rehman Arshad
Abdur Rehman Arshad

Reputation: 1

If you want to add column without loss of data you must have to alter column instead of dropping and creating new Table...

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{

    if(newVersion > oldVersion)
    {
        db.execSQL("alter table table_name add column ....");
    }
}

Further if you want to make check and balance that if column exist you can check and then alter which help not to crash your app.... Thanks

Upvotes: 0

Dalmas
Dalmas

Reputation: 26547

Use ALTER TABLE :

db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + column + " TEXT");

You can change the column type (TEXT) to whatever you need.

Upvotes: 4

bmavus
bmavus

Reputation: 892

if you drop your table, you will lose all your data. instead of this use this;

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    if(oldVersion==1){
        db.execSQL("alter table xxx add column ....");
    }
}

never drop a table. or you will lose your data!!!

Upvotes: 0

Related Questions