I am having an error in SQLite statement ON DELETE CASCADE statement in my quiz app

Question

Below is the code which in which there is an error the app contains thousands of line of code so I am putting only the statement which is having a syntax error please see the bracket inserted on delete cascade statement SQLite statement ON DELETE CASCADE and how can I remove this error Any help is appreciated.

   @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;

        final String SQL_CREATE_CATEGORIES_TABLE = "CREATE TABLE " +
                ChaptersTable.TABLE_NAME + "( " +
                ChaptersTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                ChaptersTable.COLUMN_NAME + " TEXT " +
                ")";
        final String SQL_CREATE_SUBJECTS_TABLE = "CREATE TABLE " +
                SubjectsTable.TABLE_NAME + "( " +
                SubjectsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                SubjectsTable.COLUMN_NAME + " TEXT " +
                ")";


        final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
                QuestionsTable.TABLE_NAME + " ( " +
                QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                QuestionsTable.COLUMN_QUESTION + " TEXT, " +
                QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
                QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
                QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
                QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
                QuestionsTable.COLUMN_SUBJECT_ID+ " INTEGER, " +
                "FOREIGN KEY(" + QuestionsTable.COLUMN_SUBJECT_ID+ ") REFERENCES " +
                SubjectsTable.TABLE_NAME + "(" + SubjectsTable._ID + ")" + "ON DELETE CASCADE" /*(Here im having error)*/
+
                QuestionsTable.COLUMN_CHAPTER_ID + " INTEGER, " +
                "FOREIGN KEY(" + QuestionsTable.COLUMN_CHAPTER_ID + ") REFERENCES " +
                ChaptersTable.TABLE_NAME + "(" + ChaptersTable._ID + ")" + "ON DELETE CASCADE" +
                ")";

        db.execSQL(SQL_CREATE_CATEGORIES_TABLE);
        db.execSQL(SQL_CREATE_QUESTIONS_TABLE);
        db.execSQL(SQL_CREATE_SUBJECTS_TABLE);
        fillCategoriesTable();
        fillQuestionsTable();
        fillSubjectsTable();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + ChaptersTable.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + SubjectsTable.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + QuestionsTable.TABLE_NAME);
        onCreate(db);
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

Answer 1

The correct syntax is to define the foreign keys where you define the column itself and not later:

final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
        QuestionsTable.TABLE_NAME + " ( " +
        QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        QuestionsTable.COLUMN_QUESTION + " TEXT, " +
        QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
        QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
        QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
        QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
        QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
        QuestionsTable.COLUMN_SUBJECT_ID+ " INTEGER " + "REFERENCES " +
        SubjectsTable.TABLE_NAME + "(" + SubjectsTable._ID + ") " + "ON DELETE CASCADE," +
        QuestionsTable.COLUMN_CHAPTER_ID + " INTEGER " + "REFERENCES " +
        ChaptersTable.TABLE_NAME + "(" + ChaptersTable._ID + ") " + "ON DELETE CASCADE" +
        ")";

or all the foreign keys at the end of the statement:

final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
        QuestionsTable.TABLE_NAME + " ( " +
        QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        QuestionsTable.COLUMN_QUESTION + " TEXT, " +
        QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
        QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
        QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
        QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
        QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
        QuestionsTable.COLUMN_SUBJECT_ID+ " INTEGER, " +
        QuestionsTable.COLUMN_CHAPTER_ID + " INTEGER, " +
        "FOREIGN KEY(" + QuestionsTable.COLUMN_SUBJECT_ID+ ") REFERENCES " +
        SubjectsTable.TABLE_NAME + "(" + SubjectsTable._ID + ") " + "ON DELETE CASCADE," +
        "FOREIGN KEY(" + QuestionsTable.COLUMN_CHAPTER_ID + ") REFERENCES " +
        ChaptersTable.TABLE_NAME + "(" + ChaptersTable._ID + ") " + "ON DELETE CASCADE" +//here must come '+' to remove syntax errors 
        ")";