CODEWITHSUNDEEP
databasesqliteuniqueauto-increment
Victor Rudkov
Victor Rudkov

Reputation: 388

Running out of unique IDs in sqlite database

I know that sqlite database automatically generates a unique id (autoincrement) for every record inserted.

Does anyone know if there is any possibility of running out of system unique IDs in sqlite3 database, while executing the replace query?

I mean that every piece of data in database has its own type. For example, system unique id is something like int. What would the database do with the next record, if it generates unique id equal to MAX_INT?

Thanks.

Upvotes: 4

Views: 5078

Answers (2)

Chad
Chad

Reputation: 432

http://www.sqlite.org/autoinc.html

"If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero."

Given that sqlite ids are 64-bit you could insert a new record every 100 milliseconds for the next 546 years before running out (I made up those numbers; but you get the idea).

Upvotes: 8

Matthew Flaschen
Matthew Flaschen

Reputation: 284786

The maximum possible ID is 9223372036854775807 (2^63 - 1). In any case, if it can't find a new auto-increment primary key, it will return SQLITE_FULL.

There is one other important point. If you use INTEGER PRIMARY KEY, it can reuse keys that were in the table earlier, but have been deleted. If you use INTEGER PRIMARY KEY AUTOINCREMENT, it can never reuse an auto-increment key.

Upvotes: 9

Related Questions