Tech BlogShowcase

Incrementing Values Automatically

Incrementing Values Automatically

Tableland allows developers to automatically increment a cell, with some restrictions.

Developers often want to insert a row with a cell that automatically increases by a value of 1. There are some intricacies to be aware of, but this is fully possible with Tableland SQL and makes it easy to insert a row with an automatically generated identifier.

Synopsis

The auto-incrementing feature can be achieved with a rather straightforward column — here’s what the schema should look like:

id INTEGER PRIMARY KEY

Namely, use the INTEGER PRIMARY KEY definition. Then, upon inserting a row, the id will monotonically increase if the id is not specified.

Usage

Let’s say we have an empty table named mytable with the following schema:

id INTEGER PRIMARY KEY, name TEXT

Simply insert a value and do not specify the id:

INSERT INTO mytable (name) VALUES ('Bobby Tables');
INSERT INTO mytable (name) VALUES ('Molly Tables');

The table will automatically increment the id, resulting in the following:

id
name
1
Bobby Tables
2
Molly Tables

Clarifications

The type INT will not behave in the same way as a type INTEGER in this incrementing setup. An INT will not be incremented and will throw an error upon running the insert statement defined above. Additionally, as noted in the SQL spec, AUTOINCREMENT is a reserved keyword and should not be used.

⚠️
The AUTOINCREMENT keyword as a potential feature is being further explored. Thus, it is subject to change, pending future research.

Precautions

There is one aspect to be aware of — DELETE queries. Let’s assume we have the same table as above (but as a fresh table with no rows yet):

/* `mytable` schema: id INTEGER PRIMARY KEY, name TEXT */
INSERT INTO mytable (name) VALUES ('v_1');
INSERT INTO mytable (name) VALUES ('v_2');
SELECT * FROM mytable;
1|v_1
2|v_2

/* Now, delete the last row and then insert one -- watch what happens */
DELETE FROM mytable WHERE name='v_2';
INSERT INTO mytable (name) values ('v_3');
select * from lulz;
1|v_1
2|v_3

As shown, the id for the value v_2 was 2. This row was deleted, and then a new row was inserted with a value of v_3 — its id was also created as 2, even though it was a brand new row. So, please be sure to keep this in mind when leveraging the auto-incrementing capability.

Aside from this use case, the following demonstrates the behavior when deleting some previous row that isn’t of the highest index. This outcome is rather logical, but it’s useful to at least know what will happen:

/* `mytable` schema: id INTEGER PRIMARY KEY, name TEXT */
INSERT INTO mytable (name) VALUES ('v_1');
INSERT INTO mytable (name) VALUES ('v_2');
INSERT INTO mytable (name) VALUES ('v_3');
SELECT * FROM mytable;
1|v_1
2|v_2
3|v_3

/* Now, delete the middle row (or any non-highest-index) and then insert one */
DELETE FROM mytable WHERE name='v_2';
INSERT INTO mytable (name) values ('v_4');
select * from lulz;
1|v_1
3|v_3
4|v_4