Since you're asking the question, it seems safe to assume that you expect the table to have on the order of 2^32 values? And that you'll be freeing up old values by deleting rows from the table?
First off, don't use rowid. Use "my_id INTEGER PRIMARY KEY" or something like that. rowids are an internal implementation thing. You could have a separate table with "deleted_id INTEGER PRIMARY KEY". When you delete from the main table, add my_id to the deleted table (you could perhaps do this with an after-delete trigger). When you insert into the main table, remove the lowest item from the deleted table and use that for your next id (SELECT deleted_id FROM deleted_id_table LIMIT 1 should work to find it). If there is no such item, you know that your main table is dense, so bind that input to NULL so that SQLite will choose the next id. If SQLite picks an id that is too large, it's because you have too many values to be able to identify each w/in 2^32. -scott On Wed, Dec 30, 2009 at 1:02 PM, Nikolaus Rath <nikol...@rath.org> wrote: > Hello, > > I need a unique value in the range 0 to 2^32 for every row in my table. > What is the best way to implement this in SQLite? > > I suppose that for the first 2^32 inserts I could just use the > rowid, but after that the rowid might increase above 2^32 while my > desired unique value should look for some unused value less than 2^32 > instead, so that is not an option. > > I guess the next option would be to define my own unique column instead > of using the rowid. But after 2^32 inserts, I'm getting into trouble as > well because I have to find an unused value and I don't quite know how > to do that efficiently. > > The only two ideas that come to my mind is to use a random search like > sqlite uses for the rowid, or to store the unused values in an > additional table. But I have serious doubts about both solutions. While > random search may have a good chance of success if there are 2^64 > possible rowids (it's very unlikely that more than half of them are > used, since that would already take up 8192 Petabyte), I don't think it > works very well for 2^32 possible values (when there may well be only a > couple of hundred unused ones). Using an additional table to store > unused values would require me to initialize this table with the first > 2^32 integers (taking up about 4 GB) when the main table is empty, which > is rather crude. > > The perfect solution would be to tell SQLite that it should restrict the > rowid to 2^32, but I couldn't find a documented way to do that. > > > > Does anyone have an idea how to handle a requirement like this? > > > Best, > > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C > > _______________________________________________ > 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
