sqlite3 create table error in android

Question

I am trying to create table in android using sqlite3 my open helper is this:

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

but when I run app when trying to create table this error accured:

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

this open helper is used in a datasource class to executre querys from db

I'd also highly recommend using a single string of text for your sql querry rather than concatenating with +. Using + here is fine, but in the future when you need to perform select statements it's a good habit to use a single string of sql and parameter-ize your queries if they need dynamic info. — user156213
– user156213, Commented Aug 14, 2015 at 20:23

Karakuri · Accepted Answer · 2015-08-14 20:19:45Z

2

You need to put user_id in parentheses in the FOREIGN KEY clause:

FOREIGN KEY (user_id) REFERENCES user(Id)

The entire statement should look like

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

Also you must change the name of the desc column as this is a reserved word in SQLite.

answered Aug 14, 2015 at 20:19

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

sqlite3 create table error in android

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