SQLite Forum

Because they are in the "other order".

You need to have an index on the foreign key columns.  There can be "other stuff" in those indexes.  The WITHOUT ROWID makes the table "just an index" and you need indexes on both columns and in both directions DataID <-> SubTopicID because (other than the requirement to have indexes of foreign keys) you will presumably eventually need to do lookups in BOTH directions (as in "find me all the data records where the main topic or a secondary topic is 'Work'):

Personally, I would put all the topics belonging to a Data in a link table and forget "denormalizing" one of them onto the Data record.  It adds great complication later for no material benefit.  And then you do not need triggers to enforce integrity.

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