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