How to create auto increment IDs in Cassandra

Question

We know that it is easy to create auto increment IDs in SQL databases, is there a good solution for it in Cassandra? The IDs should be for key or column name.

Answer 1

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.