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.

CL. · Accepted Answer · 2013-01-04T11:08:36.910

2

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.)

edited Jan 04 '13 at 11:08

answered Jan 04 '13 at 10:41

CL.

173,858
17
217
259

`SELECT max(id) FROM t` is very efficient if there is an index. – CL. Jan 04 '13 at 11:08
Does a trigger imply a transaction, or do I need to explicitly begin a transaction before the `UPDATE tstage_sequence...` to guard against races between clients? – Jonathan Hall Jan 04 '13 at 11:22
Triggers always run in the same transaction as the triggering command. – CL. Jan 04 '13 at 12:50
I guess my question was more whether the trigger itself would be atomic. If there is a race, the `UPDATE tstage_sequence` could fire twice, then the `UPDATE tstage` would fail with a unique key violation on the second client. I was hoping for a way to make the trigger atomic, so I don't have to check for this failure in my client code. – Jonathan Hall Jan 04 '13 at 15:35
SQLite transactions are always perfectly serialized (because there is no concurrency). – CL. Jan 04 '13 at 20:45
Which would only help if I'm willing to start a transaction before inserting anything to the 'tstage' table, right? – Jonathan Hall Jan 05 '13 at 05:39
When you don't use explicit transactions, SQLite will create an automatic transaction for each single command, but commands in triggers will be included in that transaction. – CL. Jan 07 '13 at 12:27
Ah very good. That was my question when I asked "Does a trigger imply a transaction?" – Jonathan Hall Jan 07 '13 at 14:52
@CL. i want to know that what is `rowid` in the 2nd last line of your code? – Shajeel Afzal Jun 22 '13 at 18:39

score 1 · Answer 2 · edited May 23 '17 at 12:27

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.

`(node,id)` is essentially a GUID already. I see what you're saying though--combine them into a single db field, using a bitmap of some sort. — Jonathan Hall, Jan 06 '13 at 10:59

score 1 · Answer 3 · answered Jan 05 '13 at 13:56

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.

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

3 Answers3