Menu
I'm making a wrapper of SQLite C API. And I want to return rowid as integer type. To mark error case, I need a invalid value of the rowid. Is there invalid value of SQLite rowid? Or all values in signed 64bit integers are valid for rowid? (because if it is, I have to choose another way to implement marking error case)
669
The rowid of a rowid table can be accessed (or changed) by reading or writing to any of the "rowid" or "oid" or "_rowid_" columns. Except, if there is a declared columns in the table that use those special names, then those names refer to the declared columns, not to the underlying rowid.
From the official documentation: “Rowids can change at any time and without notice. If you need to depend on your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to change”.
ROWID doesn't enumerate the rows, it gives you the row ID, which is an internal ID used by sqlite, but ROW_NUMBER() is a function that generates sequential numbers for every result set.
One can write "WITHOUT rowid" or "without rowid" or "WiThOuT rOwId" and it will mean the same thing. Every WITHOUT ROWID table must have a PRIMARY KEY. An error is raised if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
Row IDs are 64-bit signed integers, so the maximum is 0x7FFFFFFFFFFFFFFFLL. But unless a negative or zero row ID has been entered explicitly, auto-generated row IDs are always greater than zero. If you can be certain that row IDs will always be generated automatically then zero or -1 would be safe values to for error status returns.
Thinking further, I realise that the sqlite3_last_insert_rowid API call returns zero if nothing has ever been inserted into the table, thus making zero a de-facto "invalid" row ID.
101
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
