I'm trying to make my first android application. As such, I'm trying to create a test database which includes foreign keys. However, whenever I try to use foreign keys, the program crashes with the following error:
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.johan.androidtestdb/com.example.johan.androidtestdb.MainActivity}: android.database.sqlite.SQLiteException: near "_scheduleGenID": syntax error (code 1): , while compiling: CREATE TABLE task(_taskID INTEGER PRIMARY KEY AUTOINCREMENT, taskName TEXT NOT NULL, taskDesc TEXT NOT NULL, time INTEGER NOT NULL _scheduleGenID INTEGER, FOREIGN KEY (_scheduleGenID) REFERENCES scheduleGen (_scheduleGenID));
I've read the SQLite foreign key document and other answers on this site, but I still can't seem to figure out my problem. If I remove all the traces of the foreign keys, the application starts without any problems.
Here's my code:
public class MyDBHandler extends SQLiteOpenHelper {
public static final String TAG = "MyDBHelper";
public static final String TABLE_TASK = "task";
public static final String SCHE_scheduleGenID = "_scheduleGenID";
public static final String taskID = "_taskID";
public static final String taskName = "taskName";
public static final String taskDesc = "taskDesc";
public static final String taskTime = "time";
//Columns in the ScheduleGen table
public static final String TABLE_SCHEDULE_GEN = "scheduleGen";
public static final String scheduleGenID = "_scheduleGenID";
public static final String studyHours = "studyHours";
public static final String generateSchedule = "generateSchedule";
public static final String excludeMon = "excludeMon";
public static final String excludeTues = "excludeTues";
public static final String excludeWed = "excludeWed";
public static final String excludeThur = "excludeThur";
public static final String excludeFri = "excludeFri";
public static final String excludeSat = "excludeSat";
public static final String excludeSun = "excludeSun";
//Columns in the TaskSub table
public static final String TABLE_TASKSUB = "taskSub";
public static final String taskSubID = "_taskSubID";
public static final String TASKSUB_TASKID = "_taskID";
public static final String weekday = "weekday";
public static final String taskSubName = "taskSubName";
public static final String taskSubTime = "taskSubTime";
public static final String remainingTime = "remainingTime";
private static final String DATABASE_NAME = "studentapp.db";
private static final int DATABASE_VERSION = 2;
// SQL kommandon för skapandet av tabellen
private static final String SQL_CREATE_TABLE_TASK = "CREATE TABLE " + TABLE_TASK + "("
+ taskID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ taskName + " TEXT NOT NULL, "
+ taskDesc + " TEXT NOT NULL, "
+ taskTime + " INTEGER NOT NULL "
+ SCHE_scheduleGenID + " INTEGER,"
+ " FOREIGN KEY ("+SCHE_scheduleGenID+") REFERENCES "+TABLE_SCHEDULE_GEN+" ("+scheduleGenID+"));";
private static final String SQL_CREATE_TABLE_SCHEDULE_GEN = "CREATE TABLE " + TABLE_SCHEDULE_GEN + "("
+ scheduleGenID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ studyHours + " INTEGER NOT NULL, "
+ generateSchedule + " INTEGER NOT NULL, "
+ excludeMon + " INTEGER NOT NULL, "
+ excludeTues + " INTEGER NOT NULL, "
+ excludeWed + " INTEGER NOT NULL, "
+ excludeThur + " INTEGER NOT NULL, "
+ excludeFri + " INTEGER NOT NULL, "
+ excludeSat + " INTEGER NOT NULL, "
+ excludeSun + " INTEGER NOT NULL "
+");";
private static final String SQL_CREATE_TABLE_TASKSUB = "CREATE TABLE " + TABLE_TASKSUB + "("
+ taskSubID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ weekday + " INTEGER NOT NULL, "
+ taskSubName + " TEXT NOT NULL, "
+ taskSubTime + " TEXT NOT NULL, "
+ remainingTime + " TEXT NOT NULL "
+ TASKSUB_TASKID + " INTEGER,"
+ " FOREIGN KEY ("+TASKSUB_TASKID+") REFERENCES "+TABLE_TASK+" ("+taskID+"));";
public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(SQL_CREATE_TABLE_TASK);
database.execSQL(SQL_CREATE_TABLE_SCHEDULE_GEN);
database.execSQL(SQL_CREATE_TABLE_TASKSUB);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG,
"Upgrading the database from version " + oldVersion + " to " + newVersion);
// clear all data
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASK);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCHEDULE_GEN);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKSUB);
// recreate the tables
onCreate(db);
}
public void addTask(Task task){
ContentValues values = new ContentValues();
values.put(taskName, task.getMtaskName());
values.put(taskDesc, task.getMtaskDesc());
values.put(taskTime, task.getmTime());
SQLiteDatabase db = getWritableDatabase();
db.insert(TABLE_TASK, null, values);
db.close();
}
public String databaseToString(){
String dbString = "";
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_TASK + " WHERE 1";
//Cursor points to a location in your results
Cursor c = db.rawQuery(query, null);
//Move to the first row in your results
c.moveToFirst();
//Position after the last row means the end of the results
while (!c.isAfterLast()) {
if (c.getString(c.getColumnIndex("")) != null) {
dbString += c.getString(c.getColumnIndex("taskName"));
dbString += "\n";
}
c.moveToNext();
}
db.close();
return dbString;
}
0 comments:
Post a Comment