SQLite: Using ON DELETE CASCADE with FOREIGN KEYS


#1

Hi, I’m struggling to use FOREIGN KEYS with the Cordova-sqlite-storage plugin in ionic 2. The Database works but it doesn’t seem to work with FOREIGN KEYS and ON DELETE CASCADE.

This is how the Storage is created:
this.storage = new Storage(SqlStorage);

And then I’m activating sqlite foreign key support on each page (this.sqltest is outputted in the template for debugging):

this.storage.query('PRAGMA foreign_keys = ON').then((data) => { this.sqltest = 'PRAGMA works' + JSON.stringify(data.res); }, (error) => { this.sqltest = "ERROR -> " + (error.err.message); });

And in short this is how the tables are created:

this.storage.query('CREATE TABLE IF NOT EXISTS tournaments (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)'); this.storage.query('CREATE TABLE IF NOT EXISTS tests (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, tournament INTEGER, FOREIGN KEY(tournament) REFERENCES tournaments(id) ON DELETE CASCADE)')
But deleting a row from tournaments doesn’t cause the deletion of the respective row in tests.
Anyone knows how to fix this? Did I miss something? Thanks in advance!


Just to let you know: Running the PRAGMA foreign_keys = ON query with ionic serve gives me the error “could not prepare statement (23 not authorized)” but running it in the windows / android emulator seems to work.