Unable To Create Second Table In Sqlite

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 a

Solution 1:

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:

createtable purposeTable ( 
    phone integernotnull,
    cdate text notnull, 
    text1 text notnull, 
    text2 text notnull,   
    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.

Solution 2:

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:

createtable purposeTable (
phone integernotnull, 
cdate text notnull,  
text1 text notnull,
text2 text notnull,

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.

Solution 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

Solution 4:

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