Foreign Key Constraints in Android Using SQLite? on Delete Cascade

Foreign key constraints in Android using SQLite? on Delete cascade

Foreign key constraints with on delete cascade are supported, but you need to enable them.

I just added the following to my SQLOpenHelper, which seems to do the trick.

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    if (!db.isReadOnly()) {
        // Enable foreign key constraints
        db.execSQL("PRAGMA foreign_keys=ON;");
    }
}

I declared my referencing column as follows.

mailbox_id INTEGER REFERENCES mailboxes ON DELETE CASCADE

foreign key constraint ON DELETE CASCADE not working in sqlite database on android

Cascading delete isn't supported until Sqlite version 3.6.19, which is first included on Android 2.2.

Fortunately there is an alternative.

You can execute another query like this below your create table query:

db.execSQL("CREATE TRIGGER delete_days_with track BEFORE DELETE ON track "
       +  "FOR EACH ROW BEGIN"
       +         " DELETE FROM days WHERE track.day_id = days.day_id "
       +  "END;");

Note that delete_days_with_track is just a name descriptive of what the trigger does, and this is just the pattern I use; I believe you could name it anything you wish.

SQLite Delete Cascade not working: FOREIGN KEY constraint failed

From API 16+, you should enable foreign key constraint like this in SQLITEOpenHelper class:

@Override
public void onConfigure(SQLiteDatabase db){
    db.setForeignKeyConstraintsEnabled(true);
}

As the foreign key constraint is enabled, You can check if another table is not referring child table and it's foreign key is not cascade. Also ") REFERENCES " + TABLE_POST_NEW + "(_id) have you defined _id column in your TABLE_POST_NEW ?

SQLite Delete Cascade not working

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.

python sqlite foreing keys not deleting on cascade

PRAGMA FOREIGN_KEYS = ON operates on a per connection basis and this means that you must set it for every connection object that you obtain (if you want the behavior that it provides, otherwise don't set it because it may decrease performance).

Inside the Database class define:

def set_foreign_keys(self)
    self.conn.execute("PRAGMA FOREIGN_KEYS = ON")

and use it in the client:

from database import Database

db = Database("spotify.db")
db.set_foreign_keys()

db.query("insert into artistas (id_spotify, nome) values (?, ?)", ("1", "The Beatles"))
db.query("insert into musicas (id_spotify, nome, id_artista) values (?, ?, ?)", ("m1", "Hey Jude", 1))

db.query("delete from artistas where id = ?", (1,))

Foreign key ON DELETE CASCADE is not working in Android 2.2

You've told your database to delete rows in Error_Exception when the referenced rows in Location are deleted. You seem to have things backward.

If you want to delete rows in Location when rows in Error_Exception are deleted, you need to drop the foreign key constraint in Error_Exception, and add a foreign key constraint in Location.

---

Related Topics