Home / Questions / 157817
Accepted
Ibrahim Hernández Jorge
Asked: 2020-04-21 09:48:48 +0800 CST 2020-04-21 09:48:48 +0800 CST

SQLite does NOT cascade delete

  • 772

I am developing an Android app in Kotlin and I have a table PERSONSthat contains people and another table FACESthat contains faces that I associate with people. The idea is that deleting an entry PERSONSwill delete all faces associated with it. I create the tables like this:

const val SQL_CREATE_PERSONS =
            "CREATE TABLE " + "PERSONS" + "(" +
                    "ID" + " TEXT(100) PRIMARY KEY, " +
                    "NAME" + " TEXT(50)
                    "); "

    const val SQL_CREATE_FACES =
            "CREATE TABLE " + "FACES" + "(" +
                    "ID" + " TEXT(100) PRIMARY KEY, " +
                    "PERSON_ID " + "TEXT(100), " +
                    "FOREIGN KEY " + "(PERSON_ID)" + " REFERENCES " +
                    "PERSONS" + "(" +
                    "ID" + ") "+
                    "ON DELETE CASCADE " +
                    "); "

I have not forgotten to activate the Foreign Keys that are deactivated by default, I do it in the onCreate():

override fun onCreate(db: SQLiteDatabase?) {
    db?.execSQL("PRAGMA foreign_keys=ON;")
    db?.execSQL(SQL_CREATE_PERSONS)
    db?.execSQL(SQL_CREATE_FACES)
}

But when you run the application and delete an entry, PERSONStheir faces are still in the tableFACES

sqlite

1 Answers

  1. Best Answer

    From what I could find here (English version), the option on delete cascadeis not enabled by default and must be activated with each connection, that is, just before each execution of delete or update.

    To do this, the following query must be launched that activates this property for the current connection.

    PRAGMA foreign_keys = ON

    You can find more information in the following link of the official SQlite information:

    2. Enabling Foreign Key Support (English)

    In your case you are adding this support in the query to create the tables. For the support to work, it must be attached before the delete or update query.

    • 3