Creating multiple sqlite database tables in Android

Most of the Android database examples you will find on the web will usually contain only one table to demonstrate the basic database concepts.

That's great, the only problem with this is that most non-trivial database implementations will contain more than one table.

The standard database creation string for a single table will probably look a lot like the below:


private static final String CREATE_TABLE_1 =
" create table " + table1 +
" (_id integer primary key autoincrement," +
" title text not null, body text not null);";

Which is called in your DB Adapter class like this:


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE_1);
}

So what to do if you want to create more than one table?
You may do the below.. but will it work?
Note that this is one big string containing three separate create statements...


private static final String DATABASE_CREATE_MULTIPLE_TABLES =
" create table " + ITEMS_TABLE +
" (_id integer primary key autoincrement," +
" title text not null)" +

" create table " + TAGS_TABLE +
" (_id integer primary key autoincrement," +
" tagName text not null)" +

" create table " + LOCATIONS_TABLE +
" (_id integer primary key autoincrement," +
" locationName text not null, gpsCoOrds text);"
;

And then you try calling them in your DB Adapter class like so:


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(DATABASE_CREATE_MULTIPLE_TABLES);
}

So you're trying to create the three tables in one call to db.execSQL.


This appears to compile and run successfully, you can even read and write to the FIRST table that is created, but..

..when you try to read or write to any other table you will see the dreaded

'Sorry! The application Kdkddfblah (process test.Kdkddfblah) has stopped unexpectedly. Please try again'

..error message.

Uh-oh.

If you debug your application, you might see references to syntax errors 'near create', and possible a reference that the table doesn't exist.

Hmm... What went wrong?

The answer is that sqlite, and therefore the db.exec method, only lets you execute one sql command at a time. We were trying to run three sql statements in one go in db.execSQL(DATABASE_CREATE_MULTIPLE_TABLES);.

So what you need to do to fix this is move each of the above table create statements into their own strings, like this (Note that this now creates three seperate strings, unlike above):


private static final String CREATE_TABLE_1 =
" create table " + table1 +
" (_id integer primary key autoincrement," +
" title text not null, body text not null);";

private static final String CREATE_TABLE_2 =
" create table " + TAGS_TABLE +
" (_id integer primary key autoincrement," +
" tagName text not null)";

private static final String CREATE_TABLE_3 =
" create table " + LOCATIONS_TABLE +
" (_id integer primary key autoincrement," +
" locationName text not null, gpsCoOrds text);";

.. and then use these strings in your onCreate method like below, this then works.


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE_1);
db.execSQL(CREATE_TABLE_2);
db.execSQL(CREATE_TABLE_3);
}

If you find this doesn't work for you, try dropping all the tables in your database and try again, or give the database a different name, or different version number. The SQLiteOpenHelper seems to have some troubles registering that the database is to be changed. It finds a db with the same name and version number, goes 'meh' and doesn't look to see if the structure is different at all.

You can also pull the sqlite db file right off your device (or emulator) by going into the DDMS perspective in Eclipse (Window menu\ Open perspective \ Other \ DDMS), navigating to the database file which will probably be at \data\data\*Your Application Name*\databases.

There's a 'pull file' button on the top left as seen highlighted below:



.. You can then open the DB in your favourite sqlite manager (I like Sqliteman) and play around. Can can also of course, push the file back to the device if you wish.