Why can't I create second table?

Question

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.

score 0 · Accepted Answer · answered Oct 08 '22 at 14:56

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.

When I increase the database version, does it behave like new database? — Billy Istiak, Oct 09 '22 at 06:58
No when you increase the DB version, it triggers the upgrade process, if you upgrade process is drop all tables and call `onCreate` then it will behave like a new database BUT you don't have to be so brutal in the upgrade process, you could use ALTER/ADD table to keep existing data. — Andrew, Oct 09 '22 at 15:41

MikeT · Answer 2 · 2022-10-08T20:49:58.477

It appears that you are getting confused about a few things and how SQLite databases work on Android.

The database is a file that at first (when the App is installed) does not exist. However, on subsequent, use the database file does exist.

The SQLiteOpenHelper helps in managing whether or not to open an existing database file or to create the database file. If the database file is created then the onCreate method is called allowing components, such as tables to be created.

Your code indicates 2 classes that extend the SQLiteOpenHelper class BUT with the same database name (as per Constants.DB_NAME). This will cause you grief as the second used would see that the database exists.

It is important that you understand that the onCreate method only ever runs once for the lifetime of the database (if the file exists). Thus if you want to add components, such as tables, then you have to do so elsewhere. Typically in the onUpgrade method which only runs if the database version is increased and the database actually exists.

I would suggest that you use a single class that extends SQLiteOpenHelper and then create all tables in the onCreate method.

Example/Demo

The following is based upon your code and combines the employee and expense table and then as a second version introduces a third table.

column names will probably be different as the Constants class was not included in the question. That shouldn't matter though as the answer is intended to explain the principles.

The initial database helper with both the employee and the expense table:-

class TheOneAndOnlyDBHelper extends SQLiteOpenHelper {

    /* Note 2 table names */
    private static final String EMPLOYEE_TABLE = "employee";
    private static final String EXPENSE_TABLE = "expense";

    TheOneAndOnlyDBHelper(Context context) {
        super(context,Constants.DB_NAME,null,Constants.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        String query="CREATE TABLE "+EMPLOYEE_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);
        query="CREATE TABLE "+EXPENSE_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) {

    }
}

using the following in an activity:-

public class MainActivity extends AppCompatActivity {
    TheOneAndOnlyDBHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper = new TheOneAndOnlyDBHelper(this);
        /* NOTE it is not until the database is accessed (i.e. the following line) that the database is in fact created*/
        SQLiteDatabase db = dbHelper.getWritableDatabase();
    }
}

Then when first run (a new install of the App) then using App Inspection:-

i.e. the database has been created and the tables have been created.

Running the App again then exactly the same.

The following is the amended TheOneAndOnlyDBHelper to introduce the 3rd table:-

class TheOneAndOnlyDBHelper extends SQLiteOpenHelper {

    /* Note 2 table names */
    private static final String EMPLOYEE_TABLE = "employee";
    private static final String EXPENSE_TABLE = "expense";
    private static final String ANOTHER_TABLE = "another"; /* ADDED for version 2 */

    TheOneAndOnlyDBHelper(Context context) {
        super(context,Constants.DB_NAME,null,Constants.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        String query="CREATE TABLE "+EMPLOYEE_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);
        query="CREATE TABLE "+EXPENSE_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);

        /* NEEDED for new installs of the App which is now at Version 2 */
        query = "CREATE TABLE IF NOT EXISTS " + ANOTHER_TABLE + "(" + Constants.id + " INTEGER PRIMARY KEY," + Constants.other + " TEXT );";
        sqLiteDatabase.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        /* ADDED for Version 2 */
        if (i==1 && i1==2) {
            String query = "CREATE TABLE IF NOT EXISTS " + ANOTHER_TABLE + "(" + Constants.id + " INTEGER PRIMARY KEY," + Constants.other + " TEXT );";
            sqLiteDatabase.execSQL(query);
        }
    }
}

Noting that the version is increased from 1 to 2 as per:-

class Constants {
   public static final int DB_VERSION = 2; /* CHANGED FROM 1 to 2 */
   ....

When the App is rerun then as the version has been changed from 1 to 2, then the onUpgrade method is invoked and the 3rd table created as per:-

If the App is uninstalled and then rerun (as if a new person has installed the new version of the App) then as the table is now created in the onCreate method all three tables are created (same screen shot).

It is good one also. But I can't upvote. – Billy Istiak Oct 10 '22 at 09:27 — Billy Istiak, Oct 10 '22 at 09:27

Why can't I create second table?

2 Answers2