SQLite Forum

Ok, so here we are, somewhat simpler.  Uses a rowid table for the DataTopics.  It will ensure that there is always an IsMain==1 for every record.  If you do something such that this becomes untrue, the "oldest" topic becomes the main topic.  All the "management" of IsMain is "automagic" if you leave it out of insert statements, and if you specify it (as is specify IsMain = 1) then that will have "force" and previous IsMain for that Data will be reset.

```
create table Topic
(
  id     integer primary key,
  name   text collate nocase unique
);

create table Data
(
  id          integer primary key
  -- other data
);

create table DataTopics
(
  DataID     integer not null references Data(id),
  TopicID    integer not null references Topic(id),
  IsMain     integer not null on conflict replace default 0 check (IsMain in (0, 1)),
  PRIMARY KEY (DataID, TopicID),
  UNIQUE (TopicID, DataID)
);
create index MainDataTopics on DataTopics(DataID) where IsMain == 1;

-- if inserting ismain == 0 (default) change inserted record to ismain == 1 if there is no IsMain == 1 record
create trigger DataTopicsInsert0 after insert on DataTopics when new.IsMain == 0
begin
  update DataTopics
     set IsMain == 1
   where DataID == new.DataID
     and TopicID == new.TopicID
     and not exists (
                     select 1
                       from DataTopics
                      where DataID == new.DataID
                        and IsMain == 1
                    );
end;

-- if inserting ismain == 1 change previous ismain to 0
create trigger DataTopicsInsert1 after insert on DataTopics when new.IsMain == 1
begin
  update DataTopics
     set IsMain == 0
   where DataID == new.DataID
     and TopicID != new.TopicID
     and IsMain == 1;
end;

-- when changing to IsMain == 1 then reset any other IsMain == 1 to 0
create trigger DataTopicsUpdate1 after update on DataTopics when old.IsMain == 0 and new.IsMain == 1
begin
  update DataTopics
     set IsMain = 0
   where DataID == new.DataID
     and TopicID != new.TopicID
     and IsMain == 1;
end;

-- when changing to IsMain == 0 make sure we have an IsMain, if not use the oldest one
create trigger DataTopicUpdate0 after update on DataTopics when old.IsMain == 1 and new.IsMain == 0
begin
  update DataTopics
     set IsMain = 1
   where RowID == (
                      select min(rowid)
                        from DataTopics
                       where DataID == old.DataID
                    order by rowid
                       limit 1
                  )
     and not exists (
                     select 1
                       from DataTopics
                      where DataID == new.DataID
                        and IsMain == 1
                    );
end;

-- Make sure we always have IsMain record after a delete
create trigger DataTopicsDelete after delete on DataTopics when old.IsMain == 1
begin
  update DataTopics
     set IsMain = 1
   where RowID == (
                      select min(rowid)
                        from DataTopics
                       where DataID == old.DataID
                    order by rowid
                       limit 1
                  );
end;
```

Note that the previous versions may not work correctly because they included IsMain in the unique indexes and it should not have been.  Sorry 'bout that.  It also works with recursive triggers turned on.