Qutbuddin Bohra Qutbuddin Bohra - 2 months ago 12
Android Question

Syntax error while creating SQLite Table Android

I want to use foreign key for two tables, now my code as below I am getting syntax error. Please solve this error. Thanks in advance

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE "+ DB_TABLE+" ("+
KEY_TBNAME + " TEXT PRIMARY KEY NOT NULL);"

);
db.execSQL("CREATE TABLE " + DB_TABLE_CATE + " (" +
KEY_DETAIL + " TEXT NOT NULL, " +
KEY_AMOUNT + " INTEGER NOT NULL, " +
KEY_RESOURCE + " TEXT NOT NULL, " +
KEY_DATE + " TEXT NOT NULL, "+
KEY_TIME + " TEXT NOT NULL, " +
"FOREIGN KEY ("+KEY_TB_CATE+") REFERENCES "+DB_TABLE+" ("+KEY_TBNAME+");");

}


Error...


java.lang.RuntimeException: Unable to start activity ComponentInfo{app.burhani.quickaccount/app.burhani.quickaccount.HomeActivity}: android.database.sqlite.SQLiteException: near ";": syntax error (code 1): , while compiling: CREATE TABLE tablenamecate (detail TEXT NOT NULL, amount INTEGER NOT NULL, resource TEXT NOT NULL, date TEXT NOT NULL, time TEXT NOT NULL, FOREIGN KEY (tbnameis) REFERENCES tablename (tbname);

Answer

This would be real query :

CREATE TABLE DB_TABLE (  KEY_TBNAME  TEXT PRIMARY KEY NOT NULL)

CREATE TABLE  DB_TABLE_CATE  (
                KEY_DETAIL  TEXT NOT NULL,
                        KEY_AMOUNT  INTEGER NOT NULL, 
                        KEY_RESOURCE  TEXT NOT NULL, 
                        KEY_DATE  TEXT NOT NULL, 
                        KEY_TIME  TEXT NOT NULL,
                        KEY_TBNAME TEXT NOT NULL,
                       FOREIGN KEY(KEY_TBNAME) REFERENCES  DB_TABLE(KEY_TBNAME));

You have to define KEY_TBNAME and then set foreign key. I could run this query in sqlite browser and it worked for me

Comments