How to AUTOINCREMENT against another column rather than the whole table in sqlite?

Question

I'm using a table to track messages sent in several different channels.

I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'll use 'id' instead.

Here's an example of the end result that I'm looking for:

┌───────┬────────────┬────┬───────────────────┐
│ rowid │ channel_id │ id │       text        │
├───────┼────────────┼────┼───────────────────┤
│     1 │          1 │  1 │ Hello!            │
│     2 │          1 │  2 │ Message text      │
│     3 │          1 │  3 │ Beep boop         │
│     4 │          2 │  1 │ Different channel │
│     5 │          2 │  2 │ So id has reset   │
└───────┴────────────┴────┴───────────────────┘

I should then be able to INSERT INTO messages(channel, text) and have id fill itself.

Normally I'd use id INTEGER NOT NULL PRIMARY KEY or id INTEGER NOT NULL AUTOINCREMENT. However, I believe that this increments the id per-table, not per-channel.

Is there a way that I can make the id increment only such that it creates unique combinations with another column?

Answer 1

I'd just create a view on top of the table.

The view uses the analytic function row_number() to provide what you needed. You'll need a column to sort on. That could be your AUTOINCREMENT column.

https://www.sqlite.org/windowfunctions.html

select channel_id, text
  ,row_number() over (partition by channel_id order by identity_column) as id
from unnamed_table