CODEWITHSUNDEEP
javaandroidsqlsqlitecascade
XxGoliathusxX
XxGoliathusxX

Reputation: 982

Android Sqlite Delete related tables

I have the following tablescheme: //Solution edited below!

Exampe

You can see a generalization [disjoint, total]. When I delete a row in the Relation "Ziel" i would like to delete the related row in the related table.

Exampe with one entry in Koerper-Ziel:

R(Ziel) = {{1, 7, null, null, ...}}

R(Koerper-Ziel) = {{7, ...}}

delete(1) will delete both entries^

SQLite Code:

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

But it does not work yet. I think the problem is that the DELETE ON CASCADE works only in the reverse direction. How do I have to change my code?

EDIT:

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

Adding a 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);
    }

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

SOLUTION

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

Upvotes: 2

Views: 183

Answers (1)

Zach
Zach

Reputation: 1964

On delete cascade is only going to delete if there is an entry in the referenced table that is deleted. So to get the cascade effect either do your deletes from the KID, CID, TID tables or if you want to delete from the ZIEL table and have it cascade move your foreign key constraints the the KID, CID, TID tables;

I would also advise changing your schema so that the KID, CID, TID tables each has a ZIEL id, that would get rid of your need for null KID, CID, or TID values in the ZIEL table.

This is how I would change your schema (based on what I know about what you are trying to do)

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)

Now if you delete from the TABLE_ZIEL it will cascade up and remove the entry in the associated KOERPER, CARDIO, or TRAINING table

Upvotes: 1

Related Questions