SQLite DO NOT erase in Cascade

Question

SQLite DO NOT erase in Cascade

Navigation

#1 by (3 votes)

1

I am developing an Android app in Kotlin and I have a table PERSONS that contains people and another table FACES that contains faces that I relate to people. The idea is that when you delete an entry of PERSONS , all the faces associated with it will be erased. I create the tables in this way:

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 disabled by default, I do it in onCreate() :

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

But at the time of executing the application and deleting an entry of PERSONS its faces are still in the table FACES

sqlite

asked by Ibrahim Hernández Jorge 20.04.2018 в 19:48

source

1 answer

How to travel with foreach backwards forward VBA Select html element by classe

score 3 · Accepted Answer

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

For 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 support to work, it must be attached before the deletion or update query.