How can I add a second table to a database in sqlite?

Question

How can I add a second table to a database in sqlite?

I have a database examguide

and I have already created one table table_subject

and now
I want to create a second table ( table_chapter

) in this database. My question is, how do I add this table to an existing database? I have the following code. Any help was 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 does not create the second table. I want both tables in my database. it shows the 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)

+3

java android sqlite

Sandip armal patil 21 Mar 12 at 11:50

source to share

5 answers

If you want to add a table to an already existing database, use the method onUpgrade()

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 database version value that you pass to the constructor MySqliteHelper

(if you pass 1, then pass 2).

+6

Luksprog 21 Mar 12 at 11:57

source to share

Using this code you can create multiple tables

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

+3

Bhargav panchal 21 Mar 12 at 11:54

source to share

If you want to add a table to an existing database, you need to do the following: You are using a helper class. This means that in a superconductor, you are parsing the version number. The first time this helper object is created, it will call the method onCreate

that creates your initial table. Then, unless you change the version number, no function will be called when using a new instance of your class. But the helper also contains a method onUpgrade

. This function is only called when the version number you are parsing in the super-constructor is higher than the one you used earlier. So what you need to do:

Increase the constant DATABASE-VERSION

.
Cancel the function onUpgrade

and add insideDB.execsql(yourtablecreationstring)

+3

KarlKarlsom 21 Mar 12 at 12:04

source to share

You won't be able to use the method onCreate()

to create the second table as it is only called when the database is created. You can use onUpgrade()

or create a new method for this. Use on update has some limitations as it is called on change version

.

So, the best way is to add a new method to your helper class.

The method might 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        
    }

+1

S.Ahsan Apr 19 15 at 13:46

source to share

Alex lockwood · Accepted Answer · 2012-03-21T11:54:18+0000

Do one more line CREATE TABLE

and then in onCreate

, call execSQL

again:

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

Edit

To add another table to an already existing database, change your method onUpgrade

as follows. onUpgrade

called whenever the database needs to be updated; note that you must increase the value VERSION_NUMBER

(which you want to include as a private instance variable in your class) for this to take effect.

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

How can I add a second table to a database in sqlite?

More articles: