Row ID and Autoincrement

In QDB, every row of every table has a 64-bit signed integer row ID. The row ID for each row is unique among all rows in the same table. You can prevent row IDs from ever being reused in a table by using the AUTOINCREMENT keyword.

Description:

You can access the row ID of an 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 four different names: the original three names described above, 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.

When you insert a new row into a QDB table, you can either specify the row ID as part of the INSERT statement, or the database engine can assign it automatically. To specify a row ID manually, just include it in the list of values to be inserted. For example:

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 a row ID that is one larger than the largest row ID in the table prior to the insert. If the table is initially empty, then QDB uses a row ID of 1. If the largest row ID is equal to the largest possible number that can be stored in a signed 64-bit integer (9223372036854775807), then the database engine starts picking candidate IDs at random until it finds one that isn't already used.

The normal row ID selection algorithm described above will generate 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 row ID. If you ever delete rows or if you ever create a row with the maximum possible 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.

The AUTOINCREMENT Keyword

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 before existed in that same table. If the table has never before contained any data, then the database engine uses a row ID of 1. If the table has previously held a row with the largest possible row ID, then new INSERTs are not allowed and any attempt to insert a new row fails with a QDB_FULL error.

QDB keeps track of the largest row ID that a table has ever held using the special QDB_SEQUENCE table. The QDB_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the QDB_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But make sure you know what you are doing before you undertake such changes — making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm.

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected row IDs are guaranteed to have row IDs that have never been used before by the same table in the same database. And the automatically 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 stay with the default behavior, since the use of AUTOINCREMENT requires QDB to perform additional work as each row is inserted and thus causes INSERTs to run a little more slowly.