How to add second table in database in sqlite?

Question

I have database examguide and I already created one table table_subject and now
I want to create second table (table_chapter) in this database. My question is how do I add this table in the existing database? I have following code. Any help appreciated.

private static final String DATABASE_CREATE = "create table IF NOT EXISTS "
        + TABLE_SUBJECT + "( " + COLUMN_ID
        + " integer primary key autoincrement, " 
        + COLUMN_SUBJECT + " text not null, "
        + COLUMN_CHAPTER + " text, "
        + COLUMN_QUESTION + " text not null,"
        + COLUMN_OPTIONA + " text not null,"
        + COLUMN_OPTIONB + " text not null,"
        + COLUMN_OPTIONC + " text not null,"
        + COLUMN_OPTIOND + " text not null,"
        + COLUMN_CORRECT + " text not null,"
        + COLUMN_CONFIRM + " text not null);";

    private static final String DATABASE_CREATE1 = "create table IF NOT EXISTS "
    + TABLE_CHAPTER + "( " + COLUMN_ID
    + " integer primary key autoincrement, " 
    + COLUMN_SUBJECT + " text not null, "
    + COLUMN_CHAPTER + " text, "
    + COLUMN_QUESTION + " text not null,"
    + COLUMN_OPTIONA + " text not null,"
    + COLUMN_OPTIONB + " text not null,"
    + COLUMN_OPTIONC + " text not null,"
    + COLUMN_OPTIOND + " text not null,"
    + COLUMN_CORRECT + " text not null,"
    + COLUMN_CONFIRM + " text not null);";

public MySQLiteHelper open() throws SQLException 
{
    db = this.getWritableDatabase();
    return this;
}

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

}


@Override
public void onCreate(SQLiteDatabase database) {

    database.execSQL(DATABASE_CREATE);
    database.execSQL(DATABASE_CREATE1);

}

This code not create second table. I want both table in my database.
it show following error in logcat.

03-21 18:31:06.551: ERROR/Database(8255): Error inserting chapter=paging correctoption=shadow copy craete a duplicate copy of page subject=operating system question=what is shadow copy? optiona=shadow copy craete a duplicate copy of page confirm=YES optionb=sahdow copy create paging  optionc=shadow copy delete duplicate page optiond=shadow copy delete original and create shadow copy
03-21 18:31:06.551: ERROR/Database(8255): android.database.sqlite.SQLiteException: no such table: chapter: , while compiling: INSERT INTO chapter(chapter, correctoption, subject, question, optiona, confirm, optionb, optionc, optiond) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);
03-21 18:31:06.551: ERROR/Database(8255):     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
03-21 18:31:06.551: ERROR/Database(8255):     at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
03-21 18:31:06.551: ERROR/Database(8255):     at   android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
03-21 18:31:06.551: ERROR/Database(8255):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
03-21 18:31:06.551: ERROR/Database(8255):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:41)
03-21 18:31:06.551: ERROR/Database(8255):     at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1149)
03-21 18:31:06.551: ERROR/Database(8255):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1569)
03-21 18:31:06.551: ERROR/Database(8255):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1426)
03-21 18:31:06.551: ERROR/Database(8255):     at com.example.examguide.MySQLiteHelper.insertChapterData(MySQLiteHelper.java:212)
03-21 18:31:06.551: ERROR/Database(8255):     at com.example.examguide.ObjectiveAddActivity$2.onClick(ObjectiveAddActivity.java:155)

Answer 1

You will not be able to use onCreate()method to create a second table, as it is called only when the Database is created. You Can use onUpgrade()or create a new method to do this. Using on upgrade has some restrictions, as it is called when version is changed.

So the best way is to add a new method in your Helper Class.

the Method may look something like this,

    public void AddnewTable(){
    //At first you will need a Database object.Lets create it.
    SQLiteDatabase ourDatabase=this.getWritableDatabase();

    ourDatabase.execSQL(CreateTableString)//CreateTableString is the SQL Command String        
    }

Answer 2

Make another CREATE TABLE String and then in your onCreate, call execSQL once more:

database.execSQL(DATABASE_CREATE1);
database.execSQL(DATABASE_CREATE2);

Edit

To add another table to an already existing database, modify your onUpgrade method as follows. onUpgrade is called whenever the database needs to be upgraded; note that you must increment the VERSION_NUMBER (which you will want to include as a private instance variable in your class) in order for this to take effect.

@Override
public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
    db.executeSQL(DATABASE_CREATE2);
}

Answer 3

If you want to add a table to an existing database you have to do the following: You are using the helper class. This means in the superconductor you are parsing a version number. On the first creation of this helper object it will call the onCreate method which creates your initial table. Then as long you don't change the version number no function will be called when you use the create a new instance of your class. But the helper also contains an onUpgrade method. This function will only be called when the version number you parse in the super constructor is higher than the one you used before. So what you have to do:

1. Increase your constant DATABASE-VERSION.
2. Override the onUpgrade function and add inside DB.execsql(yourtablecreationstring)

Answer 4

If you want to add the table to the already existing database then use the onUpgrade() method of the MySqliteHelper:

@Override
public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
    // Create the string for the second table creation
    db.executeSQL(DATABASE_CREATE_SECOND_TABLE);
}

and also increment the value of the database version that you pass to the MySqliteHelper constructor(if you pass 1 then pass 2).

Answer 5

Using this code you can create multiple table

    private static final String ALERT_DATABASE="alerts.db";

//Database Version of Alert System  
private static final int ALERT_DATABASE_VERSION=1;

//Create alert_type table 
private static final String CREATE_ALERT_TYPE="CREATE TABLE "
        +ALERT_TYPE+"( "+ALERT_TYPE_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"
        +ALERT_TYPE_NAME+" TEXT,"+ALERT_TYPE_TONE+" VARCHAR)";  


private static final String CREATE_ALERT_INFORMATION="CREATE TABLE "
        +ALERT_INFO+"( "+ALERT_INFO_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"
        +ALERT_INFO_TITLE+" TEXT,"
        +ALERT_INFO_DATE+" VARCHAR,"
        +ALERT_INFO_TIME+" VARCHAR,"
        +ALERT_INFO_DESCRIPTION+" VARCHAR,"
        +ALERT_INFO_TYPE_ID+" VARCHAR)";


public AlertDatabase(Context context) {
    super(context, ALERT_DATABASE, null, ALERT_DATABASE_VERSION);
    // TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase alertdatabase){
    alertdatabase.execSQL(CREATE_ALERT_TYPE);
    alertdatabase.execSQL(CREATE_ALERT_INFORMATION);
}