CODEWITHSUNDEEP
androidsqlsqlite
venni
venni

Reputation: 648

Create additional table in existing SQLITE (without data loss)

I would like to add an additional table in my existing DB without loss of the data. So dropping the tables and create them new is no option. What is the best way to do that?

Upvotes: 6

Views: 3130

Answers (5)

IMRAN TAMBOLI
IMRAN TAMBOLI

Reputation: 1

First you should know about onCreate() and onUpgrade() Method

onCreate - When your database created for first time onCreate() method get called.When you are installing app for first time always onCreate() method called

onUpgrade()- When you change version of your database then onUpgrade() method call

So new user should get all the table and old user get all upgraded table and new table.

In following example suppose we are adding new table Table_5 and Altering Table_4 

public void onCreate(SQLiteDatabase db) {
 //For new User oncreate method will executed an new user get all the table
    db.execSQL( Create_Table );
    db.execSQL( Create_Table2 );
    db.execSQL( Create_Table3 );
    db.execSQL( Create_Table4 );
    db.execSQL( Create_Table5);  
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

      if(oldVersion<newVersion)
        {
           //For old user onUpgrade method is execute 
             db.execSQL( Alter_Table4);
             db.execSQL( Create_Table5);

         }     
}

Upvotes: -1

Himanshu arora
Himanshu arora

Reputation: 161

If you want to add a new table in existing database then you should do this inside onUpgrade() method like:-

 @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion == xyz) {
        db.execSQL(QUERY_CREATE_TABLE_..........NEW);
    }
}

Here xyz is the old version of database in which you want to add new table.

And remember to update the DATABASE_VERSION

When we increase the database version then onUpgrade() method calls, and using condition add the table.

And also create a new table inside onCreate() with existing tables, for fresh installs like:-

 @Override
public void onCreate(SQLiteDatabase db) {
        db.execSQL(QUERY_CREATE_TABLE_..........OLD);
        db.execSQL(QUERY_CREATE_TABLE_..........OLD);
        db.execSQL(QUERY_CREATE_TABLE_..........NEW);
}

Upvotes: 1

venni
venni

Reputation: 648

It seems to be much easier. I use the SQLiteOpenHelper. So the onCreate() is never been called if the DB already exists. When the version number increases, onUpdate() is called. In onUpdate() I have added my new table with "create table if not exists". From the documentation of SQLITE.ORG: However, if the "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and a table or view of the same name already exists, the CREATE TABLE command simply has no effect (and no error message is returned). Now the table is created and its fine.

Upvotes: 5

Comic Sans MS Lover
Comic Sans MS Lover

Reputation: 1729

Create a class that extends SQLiteOpenHelper, override onOpen(SQLiteDatabase db), create a String with the table creation query, and then use db.execSQL(String str);

Upvotes: 0

tyczj
tyczj

Reputation: 73753

the way I have done it is create a seperate database, copy everything from the current one over to the temp new one then drop the old database do whatever there and then copy the information back from the temp database.

not sure if there is another way to do it but if there is I would love to hear about it

Upvotes: 3

Related Questions