CODEWITHSUNDEEP
androiddatabaseandroid-sqliteinner-joinsql-delete
Sid
Sid

Reputation: 2862

Inner delete query sqlite

I have a database and tables of events and time table. I have linked events with time tables. Event has time table key column. Now I want to delete a time table and when the time table gets deleted the events referenced to respective time table should get delete. How to do this?

My current delete query is this which deletes only the table not events.

    public void deleteTable(TimeTable timeTable) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_TIME_TABLE, KEY_TIME_TABLE_ID + " = ?",
            new String[]{String.valueOf(timeTable.getId())});
    db.close();
}

Database helper

     public class DatabaseHelper extends SQLiteOpenHelper {



    private static final String TABLE_TIME_TABLE = "timetables";
    private static final String KEY_TABLE_TITLE = "tabletitle";
    private static final String KEY_TIME_TABLE_ID = "timetableid";
    private static final String KEY_TABLE_COLOR = "tablecolor";
    private static final String KEY_STATUS = "status";


    private static final String TABLE_EVENTS = "event";
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_START_HOURS = "starthours";
    private static final String KEY_START_MINS = "startmins";
    private static final String KEY_END_HOURS = "endhours";
    private static final String KEY_END_MINS = "endmins";

    private static final String KEY_FROM_DATE = "datefrom";
    private static final String KEY_TO_DATE = "dateto";
    private static final String KEY_LOCATION = "location";
    private static final String KEY_DAY_OF_WEEK = "dayofweek";
    private static final String KEY_NOTIFICATION_TIME = "notification";

    private static final String KEY_COLOR = "color";


    private Context context;

    public DatabaseHelper(Context context) {
        super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
        //3rd argument to be passed is CursorFactory instance
        this.context = context;
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {


        createTable(db);

    }

    public void createDatabase(){
        context.deleteDatabase(Constants.DATABASE_NAME + ".db");
        SQLiteDatabase db = this.getReadableDatabase();
    }


    public void createTable(SQLiteDatabase db){
        String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE_EVENTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_TITLE + " TEXT,"
                + KEY_FROM_DATE + " DATE,"
                + KEY_TO_DATE + " DATE,"
                + KEY_DAY_OF_WEEK + " TEXT,"
                + KEY_LOCATION + " TEXT,"
                + KEY_NOTIFICATION_TIME + " DATE,"
                + KEY_TIME_TABLE_ID + " TEXT" + ")";

        db.execSQL(CREATE_EVENTS_TABLE);

        String CREATE_TIME_TABLE = "CREATE TABLE " + TABLE_TIME_TABLE + "("
                + KEY_TIME_TABLE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_TABLE_TITLE + " TEXT,"
                + KEY_STATUS + " TEXT,"
                + KEY_TABLE_COLOR + " TEXT" + ")";

        db.execSQL(CREATE_TIME_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TIME_TABLE);
        context.deleteDatabase(Constants.DATABASE_NAME + ".db");

        createTable(db);


        // Create tables again
        //onCreate(db);
    }

}

I tried this inner delete query but it's not working:

  public void delete(TimeTable t,int id)
{
    SQLiteDatabase db = this.getWritableDatabase();

    db.execSQL(" DELETE FROM " + TABLE_TIME_TABLE + " WHERE " + KEY_TIME_TABLE_ID + " == " + id + " IN "

    + " ( " + " SELECT * FROM " + TABLE + " WHERE " + KEY_TIME_TABLE_ID + " == " + id + " ) ");

    db.close();

}

Can anyone help please?

Thank you..

Upvotes: 0

Views: 117

Answers (2)

hyena
hyena

Reputation: 765

there are two ways to go about this. first option: modify your deleteTable(TimeTable timeTable) function to delete both the parent and the child records. preferable first delete the child records (events) using the parent id then delete the parent record (time table) using the same id. here is a sample.

public void deleteTable(TimeTable timeTable) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_EVENTS, KEY_TIME_TABLE_ID + " = ?",//KEY_TIME_TABLE_ID is a foreign key 
            new String[]{String.valueOf(timeTable.getId())});
    db.delete(TABLE_TIME_TABLE, KEY_TIME_TABLE_ID + " = ?",
            new String[]{String.valueOf(timeTable.getId())});
    db.close();
}

you may want to name KEY_TIME_TABLE_ID in events table as FK_TIME_TABLE_ID make code more readable.

second option: define the foreign keys with delete cascade when creating the table (you may also alter existing table and add them).

String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE_EVENTS + "("
        + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        + KEY_TITLE + " TEXT,"
        + KEY_FROM_DATE + " DATE,"
        + KEY_TO_DATE + " DATE,"
        + KEY_DAY_OF_WEEK + " TEXT,"
        + KEY_LOCATION + " TEXT,"
        + KEY_NOTIFICATION_TIME + " DATE,"
        + KEY_TIME_TABLE_ID + " TEXT,"
        + " FOREIGN KEY (" + KEY_ID + ") references "
        + TABLE_TIME_TABLE + "(" + KEY_TIME_TABLE_ID + ") ON DELETE CASCADE)";

Note: sqlite does not support foreign keys by default, therefore run the following query every time you connect to the database.

PRAGMA foreign_keys = ON

this enables the foreign keys

Upvotes: 2

Ridcully
Ridcully

Reputation: 23655

Delete the events having a reference to the timetable entry you're going to delete. Something like that:

DELETE FROM EVENTS WHERE TIMETABLE_ID=...

Upvotes: 0

Related Questions