for a table test(i integer primary key, j integer) the new i (rowid) is as follows:
test.i = case when test.i is not null then test.i else case max(test.i) when null then 1 else max(test.i)+1 end end if you add the autoincrement keyword, then the algorithm becomes test.i = case when test.i is not null then test.i else case when sequence[test.i] is null then 1 else sequence[test.i]+1 end end sequence[test.i] = case when sequence[test.i] is null or test.i > sequence[test.i] then test.i else sequence[test.i] end and the update of sequence[test.i] occurs whether or not the transaction commits. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Luuk >Sent: Sunday, 19 October, 2014 08:10 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] 'INTEGER PRIMARY KEY' start value > >On 19-10-2014 15:27, Baruch Burstein wrote: >> Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually >> guaranteed to start from 1? Or at least from a positive number? >> > >no > >C:\temp>sqlite3 >SQLite version 3.8.4.3 2014-04-03 16:53:12 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database. >sqlite> create table test (i integer primary key, j integer); >sqlite> insert into test values (-10,-10); >sqlite> insert into test(j) values (123); >sqlite> select * from test; >-10|-10 >-9|123 >sqlite> > >or did i enter i manually? >i did with the first query.... >but not with the second one.... >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
