java - SQLite 删除级联不工作 : FOREIGN KEY constraint failed

Question

我有两个表:

子表

 public static final String SQL_CREATE_TAB_COMMENT = "CREATE TABLE " +TABLE_COMMENT+ "( " + KEY_COMMENT + "  INTEGER PRIMARY KEY AUTOINCREMENT , " + COLUMN_EMMET + " TEXT NOT NULL , " +COMMENT+  " TEXT , "+IMAGCOM+" TEXT , "+FORMAT+" TEXT NOT NULL , "+DATECMTCREATION+" TEXT , "+TAGSTATUTCMT+" TEXT , "+ COLUMN_COMMENT_KEY_POST +" INT NOT NULL , "+EMETPOST_PHONE+ " TEXT, " +DEST_PHONE+ " TEXT, "  + IDCMTEMET + " TEXT,"+VISITEDCMT+" TEXT, "+TAB_IMAGES + " TEXT, "+IS_DOWNLOADED + " TEXT,"+FILEDOC+" TEXT,  FOREIGN KEY(" + COLUMN_COMMENT_KEY_POST + ") REFERENCES " + TABLE_POST_NEW + "(_id)  ON DELETE CASCADE )";

父表

  public static final String SQL_CREATE_TAB_POST_NEW = "CREATE TABLE " +TABLE_POST_NEW+ "( " +KEY_POST+ " INTEGER PRIMARY KEY AUTOINCREMENT ," +EMMET+ " TEXT NOT NULL , " +TEXT+  " TEXT NOT NULL , "+ IMAG +" TEXT ,"+TYPE+ " TEXT ," +DEST+ " TEXT ," +IDPOSTEMET+ " TEXT NOT NULL, " +CMT_NON_LU+ " TEXT, " +DATELASTEVENT+" TEXT, " +DATECREATION+ " TEXT, " +TAGSTATUT+ " TEXT,"+COUNTER_DEST+" TEXT,"+VISITED+" TEXT,"+ NAME_GROUP+ " TEXT,"+ IDGROUP + " TEXT, "+TAB_IMAGES + " TEXT, "+IS_DOWNLOADED +" TEXT, "+COLUMN_POST_KEY_CONTACT+ " INT NOT NULL DEFAULT 0, "+LABEL_IMAGES+" TEXT,"+TAB_FILE+" TEXT)";

现在如果我删除父行我有这个错误:

FOREIGN KEY constraint failed Error Code : 787 (SQLITE_CONSTRAINT_FOEIGNKEY) Caused By : Abort due to constraint violatio

 public PosteManager openForWrite(){

    db = dbHelper.getWritableDatabase();
    //db.execSQL("PRAGMA foreign_keys=ON");
    db.setForeignKeyConstraintsEnabled(true);
    return this;
}

public int deletePoste(int posteId){

    openForWrite();
    int delete = db.delete(DbHelper.TABLE_POST_NEW, DbHelper.KEY_POST + "=" + posteId, null);
    close();

    return delete;

}

KEY_POST = _id; and I test COLUMN_COMMENT_KEY_POST = _id and COLUMN_COMMENT_KEY_POST = post_id

同样的错误

我测试了你的代码，我在 Log.d("TABLESQL","The creation SQL for table " :

D/TABLESQL: The creation SQL for table comment_tbl_new is 
    CREATE TABLE comment_tbl_new( _commentid  INTEGER PRIMARY KEY AUTOINCREMENT , Emetteur TEXT NOT NULL , comment TEXT , imageCom TEXT , format TEXT NOT NULL , Datecmtcreation TEXT , Tagstatutcmt TEXT , post_id INT NOT NULL , emetPost_phone TEXT, Dest_phone TEXT, idcmtemet TEXT,visited TEXT, tab_image TEXT, idownloaded TEXT,filedoc TEXT,  FOREIGN KEY(post_id) REFERENCES post_tbl_new(_id) )
06-14 00:11:06.845 18468-18468/com. D/TABLESQL: The creation SQL for table comment_tbl is 
    CREATE TABLE comment_tbl( _commentid  INTEGER PRIMARY KEY AUTOINCREMENT , Emetteur TEXT NOT NULL , comment TEXT , imageCom TEXT , format TEXT NOT NULL , Datecmtcreation TEXT , Tagstatutcmt TEXT , _id INT NOT NULL , emetPost_phone TEXT, Dest_phone TEXT, idcmtemet TEXT,visited TEXT, tab_image TEXT, idownloaded TEXT,filedoc TEXT,  FOREIGN KEY(_id) REFERENCES post_tbl_new(_id)  ON DELETE CASCADE )

Answer 1

从 API 16+ 开始，您应该在 SQLITEOpenHelper 类中像这样启用外键约束:

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

由于启用了外键约束，您可以检查另一个表是否没有引用子表并且它的外键不是级联的。另外 ") REFERENCES "+ TABLE_POST_NEW + "(_id) 您是否在 TABLE_POST_NEW 中定义了 _id 列？