CREATE TABLE project (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE todoitem (
  id INTEGER NOT NULL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  done BOOLEAN NOT NULL DEFAULT FALSE,

  project_id INTEGER NOT NULL,

  FOREIGN KEY (project_id)
    REFERENCES project(id)
    ON DELETE CASCADE
);

-- When I create an active project, all the others are deactivated
CREATE TRIGGER project_insert_active_trigger
  AFTER INSERT ON project
  FOR EACH ROW
  WHEN NEW.active
  BEGIN
      UPDATE project SET active = FALSE WHERE id != NEW.id;
  END;

-- When I activate a project, all the others are deactivated
CREATE TRIGGER project_update_active_trigger
  AFTER UPDATE OF active ON project
  FOR EACH ROW
  WHEN NEW.active
  BEGIN
      UPDATE project SET active = FALSE WHERE id != NEW.id;
  END;