Error : no such table : main.task_has_subtasks

15

I found that in SQLITE the subtask_time_tracking table has a wrong foreign key constraint. The constraints references the no-longer-existing table task_has_subtasks. If I understood it correctly, this was changed to table “subtasks” a while ago.
The contraints can’t be changed, but the table can be cloned with a new contraint:

ALTER TABLE subtask_time_tracking RENAME TO subtask_time_tracking_old;

CREATE TABLE subtask_time_tracking (
    "id"	INTEGER,
    "user_id"	INTEGER NOT NULL,
    "subtask_id"	INTEGER NOT NULL,
    "start"	INTEGER DEFAULT 0,
    "end"	INTEGER DEFAULT 0,
    "time_spent"	REAL DEFAULT 0,
    FOREIGN KEY("user_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY("subtask_id") REFERENCES "subtasks"("id") ON DELETE CASCADE
);

INSERT INTO subtask_time_tracking SELECT * FROM subtask_time_tracking_old;

This worked in my tests and I could then update the subtask status.
I recommend doing backups of the database before playing around with it.

1 Like