46

In Android 4.2, using SQLite 3.7.11, when I delete a row from the Quizzes table, who's schema is below, the corresponding rows in the QuizQuestions table are not deleted.

I can't figure out what's wrong. I have tried putting 

db.execSQL("PRAGMA foreign_keys = ON;");

before and after the create table statements.

Create table statements:

CREATE TABLE quizzes(quiz_name TEXT PRIMARY KEY COLLATE NOCASE);

CREATE TABLE quizQuestions(quiz_name TEXT, question_id INTEGER,
     PRIMARY KEY(quiz_name, question_id),
     FOREIGN KEY(quiz_name) REFERENCES quizzes(quiz_name) ON DELETE CASCADE,
     FOREIGN KEY(question_id) REFERENCES questions(question_id) ON DELETE CASCADE);
Taryn
  • 242,637
  • 56
  • 362
  • 405
Dan14021
  • 639
  • 1
  • 9
  • 15
  • Is there any error message? other than you noticing it's not working... – bonCodigo Nov 30 '12 at 08:12
  • No, nothing shows in LogCat. – Dan14021 Nov 30 '12 at 08:14
  • Perhaps it's due to the fact that your primary key is a compound key.... Something like this perhaps.: PRIMARY KEY (quiz_name, question_id) ON CONFLICT IGNORE, CONSTRAINT "PrimaryKey" FOREIGN KEY ("quiz_name", "question_id") REFERENCES "quizQuestions" ("quiz_name", "question_id") ON DELETE CASCADE); – bonCodigo Nov 30 '12 at 08:22
  • Check this [post out](http://stackoverflow.com/questions/5371371/how-to-set-cascade-on-sqlite-database-with-compound-primary-foreign-key) will help you greately. – bonCodigo Nov 30 '12 at 08:28
  • For me, it failed because the table was already created without the CASCADE statement, so clear the app data and create the table again. It worked for me – beginner Mar 29 '22 at 13:11

4 Answers4

83

Your database should delete rows from quizQuestions in case someone is deleting from quizzes or from questions. It will ignore the entire foreign key constraint in case foreign key support is turned off and you have just regular columns that can contain any value.

SQLite defaults to PRAGMA foreign_keys = OFF every time you open the database. It's not a property of a table or of the schema.

In case you use SQLiteOpenHelper put it in onOpen. That is the place that is called every time the database is opened. onCreate only once when the database is created.

What SQLiteOpenHelper calls when you call getWriteableDatabase for the first time is

  1. onConfigure every time, API Level >= 16 required
  2. depending on the existence and version of the database file the following is called within an transaction
    • onCreate if there is no database file. Typically, this happens only once in the entire lifetime of the app.
    • onUpgrade if the database version (PRAGMA user_version - saved inside the database file) is less then the version supplied in SQLiteOpenHelper's constructor. Happens every time you bump the version in your code.
    • Nothing if file exists and version matches.
  3. onOpen every time

If the same instance of SQLiteOpenHelper already has an open database it will just return it and nothing of above happens.

zapl
  • 63,179
  • 10
  • 123
  • 154
35

Try adding this right after opening database in your Android app:

db.execSQL("PRAGMA foreign_keys=ON");

This turns on support for foreign keys, which is necessary for ON DELETE CASCADE to work properly.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • Yes, I am doing that but it still doesn't delete.
    For example, `db.rawQuery("INSERT INTO quizzes (quiz_name) VALUES('Test1')", null);`
    `db.rawQuery("INSERT INTO questions (question) VALUES('Question1')", null);`
    `db.rawQuery("INSERT INTO quizQuestions (quiz_name, question_id) VALUES('Test1', 1)", null);`
    `db.rawQuery("DELETE FROM quizzes WHERE quiz_name = 'Test1'", null);`
    leaves the Test1 row in the quizQuestions table.     – Dan14021 Nov 30 '12 at 08:26
  • 2
    It seems like starting from API 16 (which is what you've got) you should do it in onConfigure() http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onConfigure%28android.database.sqlite.SQLiteDatabase%29 – mvp Nov 30 '12 at 08:37
  • 2
    And use [`db.setForeignKeyConstraintsEnabled(true)`](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#setForeignKeyConstraintsEnabled%28boolean%29) - but big difference is: `onConfigure` happens before `onCreate`/`onUpdate` so you have constraints enabled there already. E.g. dropping a table will trigger cascading deletes which is maybe not what you intended. – zapl Nov 30 '12 at 11:43
13

Sqlite disable foreign key constrain by default, so you need to enable it by simply override onOpen method in your DBhelper class like below

public class YourOwnDbHelper extends SQLiteOpenHelper {
    @Override
    public void onOpen(SQLiteDatabase db){
        super.onOpen(db);
        db.execSQL("PRAGMA foreign_keys=ON");
    }
}
Ken Ratanachai S.
  • 3,307
  • 34
  • 43
2

i had the same issue on visual basic!!! you have to write the command text like this:

cone.CommandText = "PRAGMA foreign_keys = ON; DELETE FROM employees WHERE cod_emp=0;"

and you have to do it everytime you delete something

user3739283
  • 21
  • 2