SQLite Forum

Yes, you are correct.  We want to make sure that the "DataID" which have IsMain == 1 are unique -- that is, there can only be 1 IsMain == 1 for each DataID.

This of course means that if you want to "change" which topic is the main topic, you have to either delete or update ismain to 0 before you can change a different one to 1 (or insert one).

You could add Insert and Update triggers that made sure that if you inserted or updated something with IsMain == 1, that the previous record for that DataID had its IsMain changed to 0.  The whole process can be automated with triggers so that the IsMain more or less takes care of itself ...

```
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, IsMain),
  UNIQUE (TopicID, DataID, IsMain)
) WITHOUT ROWID;
create unique index MainDataTopics on DataTopics(DataID) where IsMain == 1;

-- make sure no previous ismain == 1 if inserting ismain == 1
create trigger DataTopicsInsertB before insert on DataTopics when new.IsMain == 1
begin
  update DataTopics
     set IsMain = 0
   where DataID == new.DataID
     and IsMain == 1;
end;

-- if inserting ismain == 0 (default) change inserted record to ismain == 1 if there are no records for this dataid with ismain == 1
create trigger DataTopicsInsertA after insert on DataTopics when new.IsMain == 0
begin
  update DataTopics
     set IsMain == 1
    where DataID == new.DataID
      and TopicID == new.TopicID
      and (
           select count(*)
             from DataTopics
            where DataID == new.DataID
              and IsMain == 1
          ) == 0;
end;

-- when changing an ismain == 0 record to ismain == 1 make sure previous ismain == 1 are reset to 0
create trigger DataTopicsUpdate before update on DataTopics when old.IsMain == 0 and new.IsMain == 1
begin
  update DataTopics
     set IsMain = 0
   where DataID == new.DataID
     and IsMain == 1;
end;
```

This should allow you to forgo the extra conditional unique index, but I would keep it just in case anyway until I was sure that everything was working as I expected ...

Note the syntax of the definition of IsMain contains:

`  IsMain     integer not null on conflict replace default 0 check (IsMain in (0, 1))`

This means that the value should not be null, but if you try to insert a null (thus violating the constraint) the value is replaced with the default 0.  The additional check constraint makes sure that the values that get stored are either 0 or 1.  

This means that you can do this:

`insert into DataTopics (DataID, TopicID) values (1, 1);`

and the value of IsMain defaults to 0 if not specified.

Note that you can specify a conflict clause for a NOT NULL constraint but not for a CHECK constraint. <https://sqlite.org/syntax/column-constraint.html>

Adding additional triggers (after update, after delete) could ensure that there is always an IsMain == 1 for each DataID after the update, but how to choose which one to promote to IsMain == 1 is problematic (unless you maybe added a timestamp to the DataTopics table so you can choose the least recently added to promote, or something like that -- or even make it a rowid table so you could choose the one with the lowest rowid, which is basically the same thing).