Is it possible to (emulate?) AUTOINCREMENT on a compound-PK in Sqlite?

Question

According to the SQLite docs, the only way to get an auto-increment column is on the primary key.

I need a compound primary key, but I also need auto-incrementing. Is there a way to achieve both of these in SQLite?

Relevant portion of my table as I would write it in PostgreSQL:

CREATE TABLE tstage (
    id                   SERIAL NOT NULL,
    node                 INT REFERENCES nodes(id) NOT NULL,
    PRIMARY KEY (id,node),
    -- ... other columns
);

The reason for this requirement is that all nodes eventually dump their data to a single centralized node where, with a single-column PK, there would be collisions.

Answer 1

The documentation is correct. However, it is possible to reimplement the autoincrement logic in a trigger:

CREATE TABLE tstage (
    id    INT,  -- allow NULL to be handled by the trigger
    node  INT REFERENCES nodes(id) NOT NULL,
    PRIMARY KEY (id, node)
);

CREATE TABLE tstage_sequence (
    seq INTEGER NOT NULL
);
INSERT INTO tstage_sequence VALUES(0);

CREATE TRIGGER tstage_id_autoinc
AFTER INSERT ON tstage
FOR EACH ROW
WHEN NEW.id IS NULL
BEGIN
    UPDATE tstage_sequence
    SET seq = seq + 1;

    UPDATE tstage
    SET id = (SELECT seq
              FROM tstage_sequence)
    WHERE rowid = NEW.rowid;
END;

(Or use a common my_sequence table with the table name if there are multiple tables.)