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
