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);
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQLite. A foreign key with a cascade delete can only be defined in a CREATE TABLE statement.
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
Cascading deletes should not cause unexpected loss of data. If a delete requires related records to be deleted, and the user needs to know that those records are going to go away, then cascading deletes should not be used.
Cascade delete automatically deletes dependent records or sets null to ForeignKey columns when the parent record is deleted in the database. Cascade delete is enabled by default in Entity Framework for all types of relationships such as one-to-one, one-to-many and many-to-many.
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
onConfigure
every time, API Level >= 16 requiredonCreate
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.onOpen
every timeIf the same instance of SQLiteOpenHelper
already has an open database it will just return it and nothing of above happens.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With