| Updated: April 19, 2023 |
In QDB, every row of every table has a 64-bit signed integer row ID. This ID is unique for each row among all rows in the same table. You can prevent row IDs from ever being reused in a table by using the AUTOINCREMENT keyword.
You can access the row ID of a QDB table using one the special column names ROWID, _ROWID_, or OID. However, if you declare an ordinary table column to use one of those special names, then the use of that name refers to the declared column, not to the internal row ID.
If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the row ID. You can then access the row ID using any of the three special names previously listed or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.
CREATE TABLE test1(a INT, b TEXT); INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
If no row ID is specified on the insert, an appropriate row ID is created automatically. By default, QDB gives the newly created row an ID that is one larger than the largest ID in the table prior to the insert. If the table is initially empty, QDB uses a row ID of 1. If the largest row ID is equal to the maximum (i.e., largest possible) number that can be stored in a signed 64-bit integer (9223372036854775807), the database engine starts picking candidate IDs at random until it finds one that's not already used.
This row ID selection algorithm generates monotonically increasing unique row IDs as long as you never use the maximum row ID value and you never delete the entry in the table with the largest assigned row ID. If you delete some rows or create a row that uses the maximum row ID, then row IDs from previously deleted rows might be reused when you create new rows, and newly created row IDs might not be in strictly ascending order.
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT, then a slightly different row ID selection algorithm is used. The row ID chosen for the new row is one larger than the largest row ID that has ever existed in the table. If the table has never contained any data, the database engine uses a row ID of 1. If the table has previously held a row with the maximum row ID, new INSERT statements are not allowed and any attempt to insert a row fails with a QDB_FULL error.
To keep track of the largest row ID that a table has ever held, QDB uses the special QDB_SEQUENCE table. This table is created and initialized automatically whenever a normal table containing an AUTOINCREMENT column is created. The content of the QDB_SEQUENCE table can be modified with ordinary UPDATE, INSERT, and DELETE statements. But make sure you know what you are doing before you undertake such changes—modifying this table will likely disrupt the AUTOINCREMENT key generation algorithm.
The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically generated row IDs are guaranteed to have IDs that have never been used before by the same table in the same database. And the generated row IDs are guaranteed to be monotonically increasing. These are important properties in certain applications, but if your application doesn't require this behavior, you should probably use the default row ID generation algorithm. This is because the use of AUTOINCREMENT requires QDB to do extra work as each row is inserted and hence, slows down INSERT statements slightly.