java - Android Sqlite 删除相关表

Question

我有以下表格方案://下面编辑的解决方案!

你可以看到一个泛化[disjoint, total]。当我删除关系“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 );";

Answer 1

只有在被删除的引用表中有条目时，级联删除才会删除。因此，要获得级联效果，要么从 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 表中的条目