Intro to NFT Metadata
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.
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.
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
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:
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.
AUTOINCREMENTkeyword as a potential feature is being further explored. Thus, it is subject to change, pending future research.
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
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