Hello, I’m not sure if this topic has to be in this category, it’s about SQLite problem, here we go:
I have two tables: “meal” and “schedule”. They a have many-to-many relation, so I created another table: “meal_schedule” with a primary key composed by two foreign keys from that tables. I could insert in “meal_schedule” table what I wanted, like id’s which weren’t in “meal” table. I had enabled “PRAGMA foreign_keys = ON;” in my SQL file, first sentence, but I read I had to enable it in execution, so I did it. Now that works fine, I have a constraint error when I try to add an id that it’s not in meal table, but now I can’t delete a meal which has a reference in “meal_schedule” table. I use “ON DELETE CASCADE”, but it’s not working. I tried with “ON DELETE SET NULL”, but it didn’t work neither. I’m debuggin using Chrome (chrome://inspect/#devices) and the error I catch is:
{message: “sqlite3_step failure: FOREIGN KEY constraint failed”, code: 6}
But looking for that error in SQLite offical page (https://www.sqlite.org/rescode.html), code 6 means a locked error (https://www.sqlite.org/rescode.html#locked), but I’m not having any problem with that, I can execute querys normally.
Code:
SQL file
CREATE TABLE IF NOT EXISTS schedule (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE IF NOT EXISTS meal (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE IF NOT EXISTS meal_schedule (
meal_id INTEGER,
schedule_id INTEGER,
FOREIGN KEY(meal_id) REFERENCES meal(id) ON DELETE CASCADE,
FOREIGN KEY(schedule_id) REFERENCES schedule(id),
PRIMARY KEY (meal_id, schedule_id)
);
DatabaseService.ts
deleteMeal(id: number) {
let query = “DELETE FROM meal WHERE id = ?”;
return this.database.executeSql(query, [id]).then( data => {
this.loadMeals(); // this updates the list i’m susbscribe to
}).catch( error => {
this.errorOutput(“deleteMeal”, error); // this output is what I wrote before
});
}
Thanks a lot in advance!