On Sun, Apr 19, 2009 at 09:24:11AM +0200, Kees Nuyt scratched on the wall: > The maximum number of rows is 9223372036854775807. > It is limited by the maximum value of ROWID, which is an > signed 64-bit integer.
ROWID is signed, like everything in SQLite, although negative numbers are allowed, so the limit is essentially the same. $ sqlite3 SQLite version 3.6.11 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t (i INTEGER PRIMARY KEY, n integer); sqlite> insert into t values (-100000, 1); sqlite> insert into t (n) values (2); sqlite> insert into t (n) values (3); sqlite> insert into t (n) values (4); sqlite> select * from t; -100000|1 -99999|2 -99998|3 -99997|4 sqlite> For good or for bad if you use AUTOINCREMENT, any automatic values will be forced to >= 1. Ironic, given that AUTOINCREMENT burns through ROWIDs faster than the normal IPK (at least, in theory). It seems the sequence table doesn't deal with initializing to negative numbers: $ sqlite3 SQLite version 3.6.11 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t ( i INTEGER PRIMARY KEY AUTOINCREMENT, n integer); sqlite> select * from sqlite_sequence; sqlite> insert into t values (-1000000, 1); sqlite> select * from sqlite_sequence; t|0 sqlite> insert into t (n) values (2); sqlite> insert into t (n) values (3); sqlite> insert into t (n) values (4); sqlite> select * from t; -1000000|1 1|2 2|3 3|4 Although it does deal with positive numbers: $ sqlite3 SQLite version 3.6.11 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t (i INTEGER PRIMARY KEY AUTOINCREMENT, n integer); sqlite> insert into t values (100, 1); sqlite> select * from sqlite_sequence; t|100 I'm not sure if this would be considered a bug or not. The behavior isn't documented, but it isn't contrary to any documented behavior either. Of course, the limits are so big, I'm not sure it makes any practical difference. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
