1-- Add ON DELETE CASCADE to labels/task_id and blockers/task_id+blocker_id.
 2-- SQLite has no ALTER TABLE ADD CONSTRAINT, so we rebuild both tables.
 3
 4-- Drop dangling label rows before introducing stricter FK behavior.
 5DELETE FROM labels WHERE task_id NOT IN (SELECT id FROM tasks);
 6
 7CREATE TABLE labels_new (
 8    task_id TEXT,
 9    label   TEXT,
10    PRIMARY KEY (task_id, label),
11    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
12);
13
14INSERT INTO labels_new (task_id, label)
15    SELECT task_id, label FROM labels;
16
17DROP TABLE labels;
18
19ALTER TABLE labels_new RENAME TO labels;
20
21CREATE TABLE blockers_new (
22    task_id    TEXT,
23    blocker_id TEXT,
24    PRIMARY KEY (task_id, blocker_id),
25    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
26    FOREIGN KEY (blocker_id) REFERENCES tasks(id) ON DELETE CASCADE
27);
28
29INSERT INTO blockers_new (task_id, blocker_id)
30    SELECT task_id, blocker_id FROM blockers;
31
32DROP TABLE blockers;
33
34ALTER TABLE blockers_new RENAME TO blockers;