java - 我的测验应用程序中的 SQLite 语句 ON DELETE CASCADE 语句出现错误

Question

下面是代码中存在错误的应用程序包含数千行代码，所以我只放了有语法错误的语句删除此错误感谢任何帮助。

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

标签： javaandroidsqlitesyntax-errorandroid-sqlite

Answer 1

正确的语法是在定义列本身的地方定义外键，而不是稍后：

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

或语句末尾的所有外键：

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