1-- Add FOREIGN KEY (blocker_id) REFERENCES tasks(id) to the blockers table.
 2-- SQLite has no ALTER TABLE ADD CONSTRAINT, so we rebuild.
 3
 4-- Drop dangling blocker_id rows that reference nonexistent tasks.
 5DELETE FROM blockers WHERE blocker_id NOT IN (SELECT id FROM tasks);
 6
 7CREATE TABLE blockers_new (
 8    task_id    TEXT,
 9    blocker_id TEXT,
10    PRIMARY KEY (task_id, blocker_id),
11    FOREIGN KEY (task_id)    REFERENCES tasks(id),
12    FOREIGN KEY (blocker_id) REFERENCES tasks(id)
13);
14
15INSERT INTO blockers_new (task_id, blocker_id)
16    SELECT task_id, blocker_id FROM blockers;
17
18DROP TABLE blockers;
19
20ALTER TABLE blockers_new RENAME TO blockers;