Android SQLite Create Table Issue

Question

I am trying to create a table but keep getting the same error over and over and I have been googling for the answer without any success.

The query that is run in onCreate() is:

CREATE TABLE Posts 
(
    PostID TEXT PRIMARY KEY, 
    CourseID TEXT, 
    FOREIGN KEY (CourseID) REFERENCES Courses (CourseID) ON DELETE CASCADE, 
    PostStartDate INTEGER NOT NULL, 
    PostEndDate INTEGER NOT NULL, 
    TeacherID TEXT, 
    FOREIGN KEY (TeacherID) REFERENCES Teachers (TeacherID) ON DELETE CASCADE, 
    PostDescription TEXT, 
    PostHidden INTEGER DEFAULT(0)
)

The error I get is:

08-04 14:24:24.018: I/com.re.placed.DBHandler(10656): android.database.sqlite.SQLiteException: near "PostStartDate": syntax error (code 1): , while compiling: CREATE TABLE Posts (... (removed the rest of the query)

I suspect that the error might have something to do with either the foreign key or the on delete cascade. The two refenced columns are created as:

CourseID TEXT PRIMARY KEY    and    TeacherID TEXT PRIMARY KEY

And to make sure that foreign keys are used i have written the onOpen method as:

@Override
public void onOpen(SQLiteDatabase db) {
    db.execSQL("PRAGMA foreign_keys = ON;");
    super.onOpen(db);
}

Any help or suggestions are appreciated.

Icermann · Accepted Answer · 2013-08-04 14:14:04Z

2

Try putting the foreign key constraints at the end of the CREATE statement, at least it works in sqlfiddle with sqlite.

CREATE TABLE Posts 
(
    PostID TEXT PRIMARY KEY, 
    CourseID TEXT, 
    PostStartDate INTEGER NOT NULL, 
    PostEndDate INTEGER NOT NULL, 
    TeacherID TEXT, 
    PostDescription TEXT, 
    PostHidden INTEGER DEFAULT(0),
    FOREIGN KEY (TeacherID) REFERENCES Teachers (TeacherID) ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Courses (CourseID) ON DELETE CASCADE
);

answered Aug 4, 2013 at 14:14

Icermann

1813 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Android SQLite Create Table Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related