Unable to create second table in sqlite

Question

After trying all the possible solutions from blogs, forums, web pages, I've come up with this question.

Initially I created a database with a table "registrationTable", and I was able to do all the CRUD operations. Then I tried to add a second table "purposeTable", which is not getting created due to some reason.

I have tried doing the following things:

changed the database version

changed the create statement for the second table

included "PRAGMA foreign_keys = ON;" as the second table contains a foreign key

swapped the datatype of "cdate" field from text to date and vice-versa

but still the table is uncreated.

The code of my DBAdapter class is as below:

 //Table 1
private static final String TAG = "DBAdapter";
private static final String DATABASE_TABLE = "registrationTable";
private static final String DATABASE_NAME = "project1database";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE = 
    "create table registrationTable ( phone integer primary key not null, name text not null, " +
    "age integer not null, area text not null, sex text not null);";

//Table 2
private static final String PURPOSE_TABLE = "purposeTable";
private static final String PURPOSE_CREATE = "create table purposeTable ( phone integer not null, foreign key (phone) references registrationTable(phone), " +
        "cdate text not null, primary key (phone, date), text1 text not null, text2 text not null);";

private final Context context;
private SQLiteDatabase db;
private DatabaseHelper DBHelper;

public DBAdapter(Context ctx){
    context = ctx;
}
//public DBAdapter read()throws SQLException
//public DBAdapter write()throws SQLException
//public void close()
//public long insertDetails(String name, int age, String area, int phone, String sex)

public long insertPurpose(String date, String text1, String text2){
    ContentValues initialValues1 = new ContentValues();
    initialValues1.put(CDATE, date);
    initialValues1.put(TEXT1, text1);
    initialValues1.put(TEXT2, text2);
    return db.insert(PURPOSE_TABLE, null, initialValues1);
}

private static class DatabaseHelper extends SQLiteOpenHelper{

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        try{
            db.execSQL(DATABASE_CREATE);
            db.execSQL(PURPOSE_CREATE);
            db.execSQL("PRAGMA foreign_keys = ON;");
        }catch(SQLException e){
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading from version " + oldVersion + " to " + newVersion + ". All data will be deleted.");
        db.execSQL("DROP TABLE IF EXISTS registrationTable");
        db.execSQL("DROP TABLE IF EXISTS purposeTable");
        onCreate(db);
    }
}

And am calling the method insertPurpose() from another class with the below code:

dbAdapter2.write();
dbAdapter2.insertPurpose(cDate, text1, text2);
dbAdapter2.close();

And the logcat log is as below:

11-17 01:29:17.023: I/SqliteDatabaseCpp(15095): sqlite returned: error code = 1, msg = no such table: purposeTable, db=/data/data/com.android.project1/databases/project1database
11-17 01:29:17.143: E/SQLiteDatabase(15095): Error inserting text1=a text2=b cdate=17-11-2011 
11-17 01:29:17.143: E/SQLiteDatabase(15095): android.database.sqlite.SQLiteException: no such table: purposeTable: , while compiling: INSERT INTO purposeTable(text1,text2,cdate) VALUES (?,?,?)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:260)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:112)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1745)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1618)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at com.android.project1.DBAdapter.insertPurpose(DBAdapter.java:113)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at com.android.project1.Purpose3Activity$1.onClick(Purpose3Activity.java:51)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.view.View.performClick(View.java:3460)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.view.View$PerformClick.run(View.java:13955)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.os.Handler.handleCallback(Handler.java:605)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.os.Handler.dispatchMessage(Handler.java:92)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.os.Looper.loop(Looper.java:137)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at android.app.ActivityThread.main(ActivityThread.java:4340)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at java.lang.reflect.Method.invokeNative(Native Method)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at java.lang.reflect.Method.invoke(Method.java:511)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
11-17 01:29:17.143: E/SQLiteDatabase(15095):    at dalvik.system.NativeStart.main(Native Method)

Thanks in advance, if somebody can tell me where am I going wrong.

Second table is created, but data is not inserted, new log:

11-17 11:42:11.281: E/SQLiteDatabase(10172): Error inserting text1=e text2=d cdate=17-11-2011
11-17 11:42:11.281: E/SQLiteDatabase(10172): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1745)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1618)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at com.android.project1.DBAdapter.insertPurpose(DBAdapter.java:113)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at com.android.project1.Purpose3Activity$1.onClick(Purpose3Activity.java:51)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.view.View.performClick(View.java:3460)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.view.View$PerformClick.run(View.java:13955)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.os.Handler.handleCallback(Handler.java:605)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.os.Handler.dispatchMessage(Handler.java:92)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.os.Looper.loop(Looper.java:137)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at android.app.ActivityThread.main(ActivityThread.java:4340)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at java.lang.reflect.Method.invokeNative(Native Method)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at java.lang.reflect.Method.invoke(Method.java:511)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
11-17 11:42:11.281: E/SQLiteDatabase(10172):    at dalvik.system.NativeStart.main(Native Method)

Answer 1

In the create table for the second table you're trying to create a primary key over (phone, date). There's no date column there - only cdate. Also, constraints (i. e. primary/foreign key) should be after field declarations. The following syntax would work:

create table purposeTable (
phone integer not null, 
cdate text not null,  
text1 text not null,
text2 text not null,

primary key (phone, cdate),
foreign key (phone) references registrationTable(phone));  

Before you run any SQL on the device from the program, try running it interactively on a desktop-based SQLite database, see how it works. Much easier to debug that way. I recommend getting a nice desktop-based SQLite GUI, e. g. SQLiteStudio.

EDIT re: insertion failure:

You're not supplying phone. It's a non-null field, it has to be provided in the insert statement, otherwise the not null constraint fails. That's what the error says.

Answer 2

Your table constraints have to come after your column definitions:

Take note of the "column-def" and "table-constraint" loops in the above syntax diagram.

You also have a typo (date should be cdate) in your primary key.

The SQL for creating purposeTable should look like this:

create table purposeTable ( 
    phone integer not null,
    cdate text not null, 
    text1 text not null, 
    text2 text not null,   
    primary key (phone, cdate),
    foreign key (phone) references registrationTable(phone) 
);

As far as your insertPurpose method is concerned, it won't work because you have phone marked as not null in the purposeTable but insertPurpose doesn't supply a value for it. You also have phone as part of the table's primary key so you can't drop the not null. I think you should be supplying a value for phone in insertPurpose.

Answer 3

My feeling is there is some error in the sentence which creates the purpose table. Try to execute it in a shell to see if it works.

When you have several tables in one database, my personal recommendations is use different classes for different tables and utilize the sentence:

CREATE TABLE IF NOT EXISTS tableName

instead of CREATE TABLE tableName

and override the method onOpen of DataBaseHelper.

It will make your code much more clear and easy to debug.

http://www.jiahaoliuliu.com/2011/09/sqlite-create-multiple-tables-with.html

Answer 4

Try to set DATABASE_VERSION = 2 to trigger onUpgrade, and your table gets created.