[FIXED] Why can't I create second table?

Issue

My question is related to this one. Or, I can say that, it's duplicate. Since some answers hardly looks correct to me (but doesn't work).

I created a database with SQLiteOpenHelper. Now I have to create three tables. I am working in only two right now. So not get into third one though second one solves third ones problem also.

I created two page for both tables.

public class SqliteExpense extends SQLiteOpenHelper {

    private static final String TABLE="expense";
    
    public SqliteExpense(@Nullable Context context) {
        super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String query,query_1;
        query="CREATE TABLE "+TABLE+" ("+Constants.id+" INTEGER PRIMARY KEY,"+Constants.productName+" TEXT, "+Constants.productPrice+" TEXT,"+Constants.productVersion+" TEXT," +
                Constants.productPurchaseDate+" TEXT,"+Constants.productPurchaseTime+" TEXT,"+Constants.productSerial+" TEXT,"+Constants.quantityOfProduct+" TEXT)";
        sqLiteDatabase.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "+TABLE);
        onCreate(sqLiteDatabase);
    }

public class SqliteEmployee extends SQLiteOpenHelper {

    private static final String TABLE="employee";
    
    public SqliteEmployee(@Nullable Context context) {
        super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String query,query_1;
        query="CREATE TABLE "+TABLE+" ("+Constants.id+" INTEGER PRIMARY KEY,"+Constants.firstName+" TEXT, "+Constants.lastName+" TEXT,"+Constants.address+" TEXT," +
                Constants.contactNumber+" INTEGER,"+Constants.jobStatus+" TEXT,"+Constants.monthlyIncome+" INTEGER)";

        sqLiteDatabase.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "+TABLE);
        onCreate(sqLiteDatabase);
    }

Here's the both page. I created SqliteEmployee at first then SqliteExpense. If I insert data following way :

        SQLiteDatabase db=getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(Constants.firstName,firstName);
        values.put(Constants.lastName,lastName);
        values.put(Constants.contactNumber,contactNumber);
        values.put(Constants.address,address);
        values.put(Constants.jobStatus,jobStatus);
        values.put(Constants.monthlyIncome,monthlyIncome);

        long check=db.insert(TABLE,null,values);
        if (check==-1)  //check returns -1 as data don't insert
        {
            return false;

        }

        else
        {
            return true;
        }

then it works correctly for SqliteEmployee. But when I do the same for SqliteExpense (I change variables) then it doesn't work. I get an error which says table doesn't exists

no such table: expense (code 1 SQLITE_ERROR): , while compiling: INSERT INTO expense(product_version,quantity_of_product,product_purchase_date,product_purchase_time,product_serial,product_name.........

As said in the answer, to pack both tables into one SQLiteHelper. I tried doing that but it couldn't create anymore table.

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String query,query_1;
        query="CREATE TABLE "+TABLE+" ("+Constants.id+" INTEGER PRIMARY KEY,"+Constants.firstName+" TEXT, "+Constants.lastName+" TEXT,"+Constants.address+" TEXT," +
                Constants.contactNumber+" INTEGER,"+Constants.jobStatus+" TEXT,"+Constants.monthlyIncome+" INTEGER)";
        query_1="CREATE TABLE "+TABLE_EXPENSE+" ("+Constants.id+" INTEGER PRIMARY KEY,"+Constants.productName+" TEXT, "+Constants.productPrice+" TEXT,"+Constants.productVersion+" TEXT," +
                Constants.productPurchaseDate+" TEXT,"+Constants.productPurchaseTime+" TEXT,"+Constants.productSerial+" TEXT,"+Constants.quantityOfProduct+" TEXT)";

        sqLiteDatabase.execSQL(query);
        sqLiteDatabase.execSQL(query_1);
    }

I tried the code in both page. Still didn't work.

Solution

Have you been changing the value in Constants.DB_VERSION between attempts?

Once you have created the db file once it will never run onCreate again either delete the Constants.DB_NAME file or increase the value of Constants.DB_VERSION to get it run onUpgrade.

And as calling SqliteExpense second in the same execution will have the same value of Constants.DB_VERSION thus will not run the second onUpgrade.

A hack that will probably work (as you have no onDownGrade) is change

public SqliteExpense(@Nullable Context context) {
        super(context, Constants.DB_NAME, null, Constants.DB_VERSION + 1);
    }

Best to do it always in one run but again if the Constants.DB_VERSION has not been increased then you have to change the value to get onUpgrade called or delete the db file to get onCreate called.

Answered By - Andrew
Answer Checked By - Senaida (JavaFixing Volunteer)