java - Android Sqlite Delete相关表

我有以下表格：//解决方案在下面编辑！



您可以看到一个概括[不相交，总计]。
当我在关系“ Ziel”中删除一行时，我想删除相关表中的相关行。

在Koerper-Ziel中有一个条目的示例：

R（Ziel）= {{1，7，null，null，...}}

R（Koerper-Ziel）= {{7，...}}

delete（1）将删除两个条目^

SQLite代码：

private static final String CREATE_TABLE_ZIELE_KOERPER =  "CREATE TABLE " + TABLE_ZIELE_KOERPER + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_OPTION       + " INTEGER, "   +
            COLUMN_TENDENZ      + " INTEGER );";

    private static final String CREATE_TABLE_ZIELE_CARDIO =  "CREATE TABLE " + TABLE_ZIELE_CARDIO + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_EXTRA        + " TEXT );";

    private static final String CREATE_TABLE_ZIELE_TRAINING =  "CREATE TABLE " + TABLE_ZIELE_TRAINING + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_EXTRA        + " TEXT, "      +
            COLUMN_MUSKEL       + " TEXT, "      +
            COLUMN_WIEDERHOLUNGEN + " INTEGER );";

    private static final String CREATE_TABLE_ZIELE = "CREATE TABLE " + TABLE_ZIELE + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_NAME         + " TEXT, "      +
            COLUMN_ERSTELLDATUM + " LONG, "      +
            COLUMN_DEADLINE     + " LONG, "      +
            COLUMN_DONE         + " INTEGER, "   +
            COLUMN_VALUE        + " FLOAT, "     +
            COLUMN_FORTSCHRITT  + " FLOAT, "     +
            COLUMN_K_ID         + " INTEGER, "   +
            COLUMN_C_ID         + " INTEGER, "   +
            COLUMN_T_ID         + " INTEGER, "   +
            "FOREIGN KEY(" + COLUMN_K_ID + ") REFERENCES " + TABLE_ZIELE_KOERPER      + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +
            "FOREIGN KEY(" + COLUMN_C_ID + ") REFERENCES " + TABLE_ZIELE_CARDIO       + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +
            "FOREIGN KEY(" + COLUMN_T_ID + ") REFERENCES " + TABLE_ZIELE_TRAINING     + "(" + COLUMN_ID + ")  ON DELETE CASCADE  " +
            ");";

但这还行不通。
我认为问题在于DELETE ON CASCADE仅在相反方向上起作用。我该如何更改代码？

编辑：

  @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        db.execSQL("PRAGMA foreign_keys=ON");
    }

添加Koerperziel：

private long addKoerperziel(KoerperZiel ziel){
        ContentValues values = new ContentValues();
        SQLiteDatabase db = getWritableDatabase();
        //Anlegen in der Relation TABLE_ZIELE_KOERPER
        values.put(COLUMN_OPTION,       ziel.getKoerperZielOption());
        values.put(COLUMN_TENDENZ,      ziel.getTendenz());
        long kID = db.insert(TABLE_ZIELE_KOERPER, null, values);
        //Anlegen in der Relation TABLE_ZIELE mit der kID als Fremdschluessel
        values.clear();
        values.put(COLUMN_K_ID, kID);
        values.put(COLUMN_NAME,         ziel.name);
        values.put(COLUMN_ERSTELLDATUM, ziel.erstellDatum.getTime());
        values.put(COLUMN_DEADLINE,     ziel.deadline.getTime());
        values.put(COLUMN_DONE,         ziel.isDone());
        values.put(COLUMN_VALUE,        umrechnenKoerperZielValue1Add(ziel.getKoerperZielOption(), ziel.value));
        values.put(COLUMN_FORTSCHRITT,  ziel.progress);
        return db.insert(TABLE_ZIELE, null, values);
    }

编辑2：

 private static final String CREATE_TABLE_ZIELE_KOERPER =  "CREATE TABLE " + TABLE_ZIELE_KOERPER + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_OPTION       + " INTEGER, "   +
            COLUMN_TENDENZ      + " INTEGER, "   +
            COLUMN_Z_ID         + " INTEGER, "   +
            "FOREIGN KEY(" + COLUMN_Z_ID + ")   REFERENCES " + TABLE_ZIELE      + "(" + COLUMN_ID + ")  ON DELETE CASCADE"  +
            ");";

    private static final String CREATE_TABLE_ZIELE_CARDIO =  "CREATE TABLE " + TABLE_ZIELE_CARDIO + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_EXTRA        + " TEXT," +
            COLUMN_Z_ID         + " INTEGER, "   +
            "FOREIGN KEY(" + COLUMN_Z_ID + ")   REFERENCES " + TABLE_ZIELE      + "(" + COLUMN_ID + ")  ON DELETE CASCADE"  +
            ");";

    private static final String CREATE_TABLE_ZIELE_TRAINING =  "CREATE TABLE " + TABLE_ZIELE_TRAINING + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_EXTRA        + " TEXT, "      +
            COLUMN_MUSKEL       + " TEXT, "      +
            COLUMN_WIEDERHOLUNGEN + " INTEGER, " +
            COLUMN_Z_ID         + " INTEGER, "   +
            "FOREIGN KEY(" + COLUMN_Z_ID + ")   REFERENCES " + TABLE_ZIELE      + "(" + COLUMN_ID + ")  ON DELETE CASCADE"  +
            ");";

    private static final String CREATE_TABLE_ZIELE = "CREATE TABLE " + TABLE_ZIELE + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_NAME         + " TEXT, "         +
            COLUMN_ERSTELLDATUM + " LONG, "         +
            COLUMN_DEADLINE     + " LONG, "         +
            COLUMN_DONE         + " INTEGER, "      +
            COLUMN_VALUE        + " FLOAT, "        +
            COLUMN_FORTSCHRITT  + " FLOAT );";

解

 private static final String CREATE_TABLE_ZIELE_KOERPER =  "CREATE TABLE " + TABLE_ZIELE_KOERPER + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, "    +
            COLUMN_OPTION       + " INTEGER, "                              +
            COLUMN_TENDENZ      + " INTEGER, "                              +
            COLUMN_Z_ID         + " INTEGER, "                              +
            "FOREIGN KEY(" + COLUMN_Z_ID + ")   REFERENCES " + TABLE_ZIELE      + "(" + COLUMN_ID + ")  ON DELETE CASCADE"  +
            ");";

    private static final String CREATE_TABLE_ZIELE_CARDIO =  "CREATE TABLE " + TABLE_ZIELE_CARDIO + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, "    +
            COLUMN_EXTRA        + " TEXT,"                                  +
            COLUMN_Z_ID         + " INTEGER, "                              +
            "FOREIGN KEY(" + COLUMN_Z_ID + ")   REFERENCES " + TABLE_ZIELE      + "(" + COLUMN_ID + ")  ON DELETE CASCADE"  +
            ");";

    private static final String CREATE_TABLE_ZIELE_TRAINING =  "CREATE TABLE " + TABLE_ZIELE_TRAINING + "(" +
            COLUMN_ID               + " INTEGER PRIMARY KEY AUTOINCREMENT, "    +
            COLUMN_EXTRA            + " TEXT, "                                 +
            COLUMN_MUSKEL           + " TEXT, "                                 +
            COLUMN_WIEDERHOLUNGEN   + " INTEGER, "                              +
            COLUMN_Z_ID             + " INTEGER, "                              +
            "FOREIGN KEY(" + COLUMN_Z_ID + ")   REFERENCES " + TABLE_ZIELE      + "(" + COLUMN_ID + ")  ON DELETE CASCADE"  +
            ");";

    private static final String CREATE_TABLE_ZIELE = "CREATE TABLE " + TABLE_ZIELE + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_NAME         + " TEXT, "         +
            COLUMN_ERSTELLDATUM + " LONG, "         +
            COLUMN_DEADLINE     + " LONG, "         +
            COLUMN_DONE         + " INTEGER, "      +
            COLUMN_VALUE        + " FLOAT );";

最佳答案

在删除时，级联仅在引用表中有一个已删除的条目时才删除。因此，要获得级联效果，请从KID，CID，TID表中删除，或者如果要从ZIEL表中删除，并使其级联将外键约束移动到KID，CID，TID表中；

我还建议更改架构，以使KID，CID，TID表每个都有一个ZIEL ID，这将消除您对ZIEL表中空KID，CID或TID值的需求。

这就是我要更改架构的方式（基于我对您要执行的操作的了解）

TABLE_ZIELE

COLUMN_ID, COLUMN_NAME, COLUMN_ERSTELLDATUM, COLUMN_DEADLINE, COLUMN_DONE, COLUMN_VALUE, COLUMN_FORTSCHRITT

TABLE_ZIELE_KOERPER

COLUMN_ID, COLUMN_OPTION, COLUMN_TENDENZ, ZIEL_ID -> FOREIGN KEY(ZIEL_ID) REFERENCES ZIEL(COLUMN_ID)

TABLE_ZIELE_CARDIO

COLUMN_ID, COLUMN_EXTRA, ZIEL_ID -> FOREIGN KEY(ZIEL_ID) REFERENCES ZIEL(COLUMN_ID)

TABLE_ZIELE_TRAINING

COLUMN_ID, COLUMN_EXTRA, COLUMN_MUSKEL, COLUMN_WIEDERHOLUNGEN, ZIEL_ID -> FOREIGN KEY(ZIEL_ID) REFERENCES ZIEL(COLUMN_ID)

现在，如果您从TABLE_ZIEL中删除，它将级联并删除关联的KOERPER，CARDIO或TRAINING表中的条目