CODEWITHSUNDEEP
androidsqlite
Step
Step

Reputation: 53

SQLite Database for Android SDK

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 "

        +");";

Upvotes: 0

Views: 66

Answers (1)

David M
David M

Reputation: 2541

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+"));";

Upvotes: 1

Related Questions