A proper UPSERT command would be very useful. Here is the simple ON DELETE
example showing the problem using the single REPLACE command, even with
deferred foreign keys.
CREATE TABLE a(
id_a INTEGER PRIMARY KEY AUTOINCREMENT,
data_a);
CREATE TABLE b(
id_b INTEGER PRIMARY KEY AUTOINCREMENT,
id_a REFERENCES a ON DELETE CASCADE,
data_b);
INSERT INTO a(data_a) VALUES('123'),('456'),('789');
INSERT INTO b(id_a,data_b) VALUES(1,'XXX'),(2,'YYY'),(3,'ZZZ');
PRAGMA foreign_keys = on;
PRAGMA defer_foreign_keys = on;
BEGIN;
REPLACE INTO a VALUES (2,'654');
END;
SELECT rowid,* FROM a;
SELECT rowid,* FROM b;
1|1|123
2|2|654
3|3|789
1|1|1|XXX
3|3|3|ZZZ
On Wednesday, January 20, 2016 7:29 AM, Hick Gunter <hick at scigames.at> wrote:
>REPLACE will delete the offending row in the referenced table and insert a new
>one with a newly >generated rowid. If the foreign key references the rowid,
>the DELETE CASCADE will be invoked because >there is no longer a record with
>the referenced rowid.
