SQLite Forum

Start rowid from 0
Login

Start rowid from 0

(1) By Tim (Timmmm) on 2020-11-17 21:24:28 [link] [source]

I am using SQLite as a log format. Some tables have an INTEGER PRIMARY KEY field that should start from 0. SQLite can almost do this automatically, by inserting NULL for that column, but unfortunately it starts from 1 so you have to explicitly insert a value. This is a bit more tedious since it involves me tracking extra state.

I could not find any way to make it start from 0 instead. I found some references to sqlite_sequence but it appears that that only applies to AUTOINCREMENT fields and my field is not AUTOINCREMENT so sqlite_sequence is never created.

Someone on StackOverflow asked for the same thing but there were no helpful answers.

Is it possible to achieve this, and if not could you add a feature to do it?

(2) By Larry Brasfield (LarryBrasfield) on 2020-11-17 22:55:43 in reply to 1 [link] [source]

Perhaps: sqlite> create table LoveMyZero( nzid integer primary key, znum integer generated always as (nzid-1) virtual, payload text); sqlite> insert into LoveMyZero values (null, "First entry"); sqlite> select * from LoveMyZero; 1|0|First entry sqlite>

(3) By jake on 2020-11-17 23:20:05 in reply to 1 [link] [source]

Here are 2 ideas.

Using an AFTER INSERT trigger (note the use of INT PRIMARY KEY instead of INTEGER PRIMARY KEY):

sqlite> CREATE TABLE t1(
   ...>   id INT PRIMARY KEY
   ...> );
sqlite>
sqlite> CREATE TRIGGER t1_id AFTER INSERT ON t1 BEGIN
   ...>   UPDATE t1 SET id = rowid-1 WHERE rowid = new.rowid;
   ...> END;
sqlite>
sqlite> INSERT INTO t1 VALUES(NULL);
sqlite> INSERT INTO t1 VALUES(NULL);
sqlite> INSERT INTO t1 VALUES(NULL);
sqlite>
sqlite> SELECT * FROM t1;
+----+
| id |
+----+
| 0  |
| 1  |
| 2  |
+----+

Using generated columns:

sqlite> CREATE TABLE t2(
   ...>   pk INTEGER PRIMARY KEY,
   ...>   id INT GENERATED ALWAYS AS (pk-1) STORED UNIQUE
   ...> );
sqlite>
sqlite> INSERT INTO t2 VALUES(NULL);
sqlite> INSERT INTO t2 VALUES(NULL);
sqlite> INSERT INTO t2 VALUES(NULL);
sqlite>
sqlite> SELECT * FROM t2;
+----+----+
| pk | id |
+----+----+
| 1  | 0  |
| 2  | 1  |
| 3  | 2  |
+----+----+

(4) By Keith Medcalf (kmedcalf) on 2020-11-18 00:09:31 in reply to 1 [source]

Why does it need to start from 0 (zero)? Why not start from 47?

This seems to indicate that you are using the INTEGER PRIMARY KEY not as a pseudo-key but are overloading it with some sort of inapplicable meaning. This is a failure of design.

Have you considered fixing your thought process to comply with reality rather than to try to bend reality to your assumptions?

(6) By tom (younique) on 2020-11-18 11:07:52 in reply to 4 [link] [source]

The reason is quite clear: usually, IT-numbering starts at zero. Chances are high for an off-by-one-error when mixing 0-based and 1-based indices.

(8) By Warren Young (wyoung) on 2020-11-18 11:15:49 in reply to 6 [link] [source]

Chances are high for an off-by-one-error

Only when you're investing semantic meaning into the value.

If I say the id is 2 instead of 3, and I tell you that the difference matters, then we have to ask "2 of what?" Are these countable objects?

The last time I tried to use an ID column for semantic meaning, I had to rebuild the database because it was an analog TV channel number, which then blew my 8-bit column width when we went first to digital cable and its logical 900 channels, then to direct digital systems with channel.subchannel notation, then to IPTV with multicast IP addresses as the "channel."

(All of this is pre-SQLite, so we couldn't just store strings in a formerly numeric column, even had that made sense in our program, which is written in a statically-typed programming language.)

So, what is your example of a case where 0-based IDs semantically matters?

(9) By Keith Medcalf (kmedcalf) on 2020-11-18 17:27:36 in reply to 6 [link] [source]

The only "meaning" that can be attributed to an INTEGER PRIMARY KEY column is that it uniquely identifies a particular "row" (tuple) in the "table" (relation).

Given how these number are auto-generated if the INTEGER PRIMARY KEY is entirely and always computer generated and has not been futzed with (updated/changed/assigned) by an external source, and the number of insertions has not overflowed a signed 64-bit integer, that a numerically greater INTEGER PRIMARY KEY indicates that particular "row" (tuple) was inserted into the relation subsequently to the insertion of any "row" (tuple) in that relation with a lesser INTEGER PRIMARY KEY.

This holds notwithstanding the initial value.

Any other meaning is merely an artificial overload of meaning assigned by the designer. It would be prudent to avoid assigning artificial overloads of meaning unless the can be held invariant for all eternity.

If you assume that your INTEGER PRIMARY KEY maps to an array, then how do you deal with deleting "element 0"? You will have to renumber all your rows (INTEGER PRIMARY KEY) to maintain the mapping.

It appears that this is quite a common error that arises solely because of defective thinking!

(5) By Richard Hipp (drh) on 2020-11-18 01:03:23 in reply to 1 [link] [source]

Here is a patch:

Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -4938,11 +4938,11 @@
       rc = sqlite3BtreeLast(pC->uc.pCursor, &res);
       if( rc!=SQLITE_OK ){
         goto abort_due_to_error;
       }
       if( res ){
-        v = 1;   /* IMP: R-61914-48074 */
+        v = 0;
       }else{
         assert( sqlite3BtreeCursorIsValid(pC->uc.pCursor) );
         v = sqlite3BtreeIntegerKey(pC->uc.pCursor);
         if( v>=MAX_ROWID ){
           pC->useRandomRowid = 1;

The comment on the old line indicates that the use of 1 is documented behavior:

And because it is documented, that means it won't change without a very good reason. But you can make the change in your own private copies of SQLite, if you want.

(7) By tom (younique) on 2020-11-18 11:10:08 in reply to 5 [link] [source]

Imho, the solution would by quite simple. MySQL has these constructs:

CREATE TABLE your_table () AUTO_INCREMENT = 6000;

ALTER TABLE your_table AUTO_INCREMENT = 6000;