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

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_autoincAFTER INSERT ON tstageFOR EACH ROWWHEN NEW.id IS NULLBEGIN    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.)

A trigger works, but is complex. More simply, you could avoid serial ids. One approach, you could use a GUID. Unfortunately I couldn't find a way to have SQLite generate the GUID for you by default, so you'd have to generate it in your application. There also isn't a GUID type, but you could store it as a string or a binary blob.

Or, perhaps there is something in your other columns that would serve as a suitable key. If you know that inserts won't happen more frequently than the resolution of your timestamp format of choice (SQLite offers several, see section 1.2), then maybe (node, timestamp_column) is a good primary key.

Or, you could use SQLite's AUTOINCREMENT, but set the starting number on each node via the sqlite_sequence table such that the generated serials won't collide. Since rowid is SQLite is a 64-bit number, you could do this by generating a unique 32-bit number for each node (IP addresses are a convenient, probably unique 32 bit number) and shifting it left 32 bits, or equivalently, multiplying it by 4294967296. Thus, the 64-bit rowid becomes effectively two concatenated 32-bit numbers, NODE_ID, RECORD_ID, guaranteed to not collide unless one node generates over four billion records.

How about...

ASSUMPTIONS

• Only need uniqueness in PK, not sequential-ness
• Source table has a PK

Create the central table with one extra column, the node number...

CREATE TABLE tstage (    node  INTEGER NOT NULL,    id    INTEGER NOT NULL,   <<< or whatever the source table PK is    PRIMARY KEY (node, id)       :);

When you rollup the data into the centralized node, insert the number of the source node into 'node' and set 'id' to the source table's PRIMARY KEY column value...

INSERT INTO tstage (nodenumber, sourcetable_id, ...);

There's no need to maintain another autoincrementing column on the central table because nodenumber+sourcetable_id will always be unique.