I ran into a problem with an Android application, where I needed to create multiple database tables. There’s a small catch to remember that may save you a headache – but it’s just a matter of coding the database access layer right.

Your database access layer may define SQL code for database tables like this:

private static final String DATABASE_CREATE =
    "CREATE TABLE hotel (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255)); "
  + "CREATE TABLE province (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255)); ";

The SQL is then executed so:

public void onCreate(SQLiteDatabase db)
{
  db.execSQL(DATABASE_CREATE);
}

However, there’s a danger here. Android does not actually allow you to execute more than one SQL statement with a single execution. It is necessary to split up the table creation SQL into two separate statements, which are executed one by one:

private static final String CREATE_TABLE_HOTEL =
  "CREATE TABLE hotel (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255)); ";
private static final String CREATE_TABLE_PROVINCE =
  "CREATE TABLE province (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255)); ";

The statements are then executed like this:

public void onCreate(SQLiteDatabase db)
{
  db.execSQL(CREATE_TABLE_HOTEL);
  db.execSQL(CREATE_TABLE_PROVINCE);
}

If you don’t do this, the second table will never get created, and no warning is issued about it.