SQLite Database for Android SDK

Question

I am writing a database that seems to work with testing without the foreign keys but once i enter the foreign keys it seems to mess up. So far I am testing a users table and have the following code;

public static final String TABLE_USERS = "users";
public static final String COLUMN_USER_ID = "_id";
public static final String COLUMN_USER_NAME = "user_name";
public static final String COLUMN_USER_DOB = "DOB";
public static final String COLUMN_USER_PLAN_ID = COLUMN_PLAN_ID;
private static final String SQL_CREATE_TABLE_USER = "create table " + TABLE_USERS+ " ("
 + COLUMN_USER_ID + " INTEGER PRIMARY KEY NOT NULL, "
 + COLUMN_USER_NAME + " TEXT NOT NULL, "
 + COLUMN_USER_DOB + " TEXT NOT NULL, "
 + COLUMN_USER_PLAN_ID+ " INTEGER FOREIGN KEY ("+COLUMN_USER_PLAN_ID+") REFERENCES plan("+COLUMN_PLAN_ID+")"
 + ");";

I am testing with the following code however it never gets to a stage where i can test it.

public long insertRow(String name, String DOB, int planId) {
 ContentValues initialValues = new ContentValues();
 initialValues.put(COLUMN_USER_NAME, name);
 initialValues.put(COLUMN_USER_DOB, DOB);
 initialValues.put(COLUMN_USER_PLAN_ID, planId);
 return db.insert(TABLE_USERS , null, initialValues);
}

Have i made an error with the syntax somewhere or something? The emulator just refuses to open the test activity

plan table;

public static final String TABLE_PLAN = "plan";
public static final String COLUMN_PLAN_ID = "plan_id";
public static final String COLUMN_PLAN_DESCRIPTION = "description";
public static final String COLUMN_PLAN_DISTANCE = "distance";
public static final String COLUMN_PLAN_LEVEL = "level";
public static final String COLUMN_PLAN_COMPLETED = "completed";
public static final String COLUMN_PLAN_DATE = "date";
private static final String SQL_CREATE_TABLE_PLAN = "CREATE TABLE " + TABLE_PLAN + "("
 + COLUMN_PLAN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
 + COLUMN_PLAN_DESCRIPTION + " TEXT NOT NULL, "
 + COLUMN_PLAN_DISTANCE + " INTEGER NOT NULL, "
 + COLUMN_PLAN_LEVEL + " INTEGER NOT NULL, "
 + COLUMN_PLAN_COMPLETED+ " TEXT NOT NULL, "
 + COLUMN_PLAN_DATE + " TEXT NOT NULL "
 +");";

• Christopher: Did you uninstall the app on your emulator before installing your test activity? Did you implement a correct onUpgrade-behaviour for your SQLiteOpenHelper?
• David M: what's the value of COLUMN_PLAN_ID? if it's "_id" you already have a column of that name in the table. can you post the LogCat showing the error?
• Step: @christopher, yeh to both,
• Step: @david m, plan id is plan_id
• Christopher: Could you provide any logfile? What's the exception?

Answer 1

change the you definition of SQL_CREATE_TABLE_USER to be...

private static final String SQL_CREATE_TABLE_USER = "create table " + TABLE_USERS+ " ("
 + COLUMN_USER_ID + " INTEGER PRIMARY KEY NOT NULL, "
 + COLUMN_USER_NAME + " TEXT NOT NULL, "
 + COLUMN_USER_DOB + " TEXT NOT NULL, "
 + COLUMN_USER_PLAN_ID+ " INTEGER, "
 + "FOREIGN KEY ("+COLUMN_USER_PLAN_ID+") REFERENCES " + TABLE_PLAN + " ("+COLUMN_PLAN_ID+"));";