Make SQLite generate unique values of ROWID

I have table orders in an SQLite database:

CREATE TABLE orders
(
    clientId INTEGER NOT NULL,
    listId INTEGER,
    exchangeId NOT NULL,
    marketId NOT NULL,
    id INTEGER NOT NULL,
    accountType NOT NULL,
    clientGuid NOT NULL,
    side NOT NULL,
    type NOT NULL,
    status NOT NULL,
    price NOT NULL,
    stopPrice NOT NULL,
    amount NOT NULL,
    filled NOT NULL,
    cost NOT NULL,
    createTime NOT NULL,
    updateTime NOT NULL,
    PRIMARY KEY(clientId)
);

When I create a record, delete it and then create another new record I get the same clientId (14443):

SQL> INSERT INTO orders (listId, exchangeId, marketId, id, accountType, clientGuid, side, type, status, price, stopPrice, amount, filled, cost, createTime, updateTime) VALUES (19, 'myexchange', 'mymarket', 1163221156, 0, '93c39dd5-bc89-422a-b5e0-a34289ccff44', 0, 0, 1, 778856, 0, 160, 0, 0, 1741356070112000000, 1741356070112000000);


SQL> select * from orders where marketid='mymarket';

(14443, 19, 'myexchange', 'mymarket', 1163221156, 0, '93c39dd5-bc89-422a-b5e0-a34289ccff44', 0, 0, 1, 778856, 0, 160, 0, 0, 1741356070112000000, 1741356070112000000)

SQL> DELETE FROM orders WHERE clientId=14443;


SQL> INSERT INTO orders (listId, exchangeId, marketId, id, accountType, clientGuid, side, type, status, price, stopPrice, amount, filled, cost, createTime, updateTime) VALUES (19, 'myexchange', 'mymarket', 1163221156, 0, '93c39dd5-bc89-422a-b5e0-a34289ccff44', 0, 0, 1, 778856, 0, 160, 0, 0, 1741356070112000000, 1741356070112000000);


SQL> select * from orders where marketid='mymarket';

(14443, 19, 'myexchange', 'mymarket', 1163221156, 0, '93c39dd5-bc89-422a-b5e0-a34289ccff44', 0, 0, 1, 778856, 0, 160, 0, 0, 1741356070112000000, 1741356070112000000)

But there is the following in the docs:

The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.

So according to the docs clientId of recreated record should be 14444, right?

The fact that new clientId matches a previously deleted clientId leads to potential bugs if an clientId of a deleted orders stays somewhere in the app by mistake.

Is there a way to make SQLite generate unique clientId?

Solution

You're using the normal ROWID selection algorithm, as definde in section 2 of the documentaion you linked to. It states that (I added bolding for emphasis):

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

The passage you quoted is from section 3, referencing the AUTOINCREMENT keyword. For it to apply, you need to explicilty use this keyword:

CREATE TABLE orders
(
    clientId INTEGER PRIMARY KEY AUTOINCREMENT, -- Explciitly defined inline primary key
    listId INTEGER,
    exchangeId NOT NULL,
    marketId NOT NULL,
    id INTEGER NOT NULL,
    accountType NOT NULL,
    clientGuid NOT NULL,
    side NOT NULL,
    type NOT NULL,
    status NOT NULL,
    price NOT NULL,
    stopPrice NOT NULL,
    amount NOT NULL,
    filled NOT NULL,
    cost NOT NULL,
    createTime NOT NULL,
    updateTime NOT NULL
    -- No addition primary key definition
);