Syntax error while creating SQLite Table Android

Question

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 1

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

Syntax error while creating SQLite Table Android

Sign up