android的Sqlite3创建表错误

Question

我试图在android中使用sqlite3创建表我打开的帮助器是:

public class TaskOpenHelper extends BaseOpenHelper {
    public static final String TABLE_NAME = "tasks";
    public static final String COL_ID = "id";
    public static final String COL_USER_ID = "user_id";
    public static final String COL_TITLE = "title";
    public static final String COL_DATE = "date";
    public static final String COL_TIME = "time";
    public static final String COL_IS_DONE = "is_done";
    public static final String COL_IS_IPM = "is_ipm";
    public static final String COL_DESC = "desc";
    public static final String COL_REGISTER_TIME = "register_time";
    public static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
            "(" +
            COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            COL_USER_ID + " INTEGER NOT NULL," +
            COL_TITLE + " TEXT NOT NULL," +
            COL_DATE + " TEXT ," +
            COL_TIME + " TEXT ," +
            COL_IS_DONE + " INTEGER ," +
            COL_IS_IPM + " INTEGER ," +
            COL_DESC + " TEXT," +
            COL_REGISTER_TIME + " TEXT," +
            "FOREIGN KEY " + COL_USER_ID + " REFERENCES " + UserOpenHelper.TABLE_NAME + "(" + UserOpenHelper.COL_ID + ")" +
            ");";

    public TaskOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_TABLE);
    }
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(sqLiteDatabase);
    }

，但当我运行应用程序时，试图创建表这个错误发生:

Caused by: android.database.sqlite.SQLiteException: near "user_id": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS tasks(id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,title TEXT NOT NULL,date TEXT ,time TEXT ,is_done INTEGER ,is_ipm INTEGER ,desc TEXT,register_time TEXT,FOREIGN KEY user_id REFERENCES user(Id));

这个打开的帮助器在数据源类中用于从db

Answer 1

您需要将user_id放在FOREIGN KEY子句中的括号中:

FOREIGN KEY (user_id) REFERENCES user(Id)

整个语句看起来应该像

CREATE TABLE IF NOT EXISTS tasks(id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    date TEXT,
    time TEXT,
    is_done INTEGER,
    is_ipm INTEGER,
    desc TEXT,
    register_time TEXT,
    FOREIGN KEY user_id REFERENCES user(Id));

你还必须更改desc列的名称，因为这是一个保留字在SQLite