android - Android SQLite创建表问题

Question

我试图创建一个表，但是不断出现相同的错误，我一直在寻找答案，但没有成功。

在onCreate()中运行的查询为:

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)
)

我得到的错误是:

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)

我怀疑该错误可能与外键或on delete级联有关。创建两个引用列:

CourseID TEXT PRIMARY KEY    and    TeacherID TEXT PRIMARY KEY

为了确保使用外键，我将onOpen方法编写为:

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

任何帮助或建议，表示赞赏。

Answer 1

尝试将外键约束放在CREATE语句的末尾，至少它在带sqlite的sqlfiddle中有效。

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