How to create auto increment IDs in Cassandra

by

How about the following, using Cassandra’s Lightweight transactions

1 – Create IDs table:

CREATE TABLE ids (
  id_name varchar,
  next_id int,
  PRIMARY KEY (id_name)
)

2 – Insert every id you’d like to use a global sequence with

For example:

INSERT INTO ids (id_name, next_id)
VALUES ('person_id', 1)

3 – Then, when inserting to a table where you’d like to use an auto-incremented key, do the following:

3.1 – Get the next_id from the ids table:

SELECT next_id FROM ids WHERE id_name="person_id"

Let’s say the result is next_id = 1

3.2 – Increment next_id, the following way:

UPDATE ids SET next_id = 2 WHERE id_name="person_id" IF next_id = 1

The result should look like this:

[{[applied]: True}]

If it was updated successfully, OR

[{[applied]: False, next_id: 2}]

If someone else has already updated it.

So, if you got True, use id ‘1’ – it is yours. Otherwise, increment next_id (or just use the returned next_id) and repeat the process.

Leave a Comment