Actually a UUID or a GUID has a 100% certainty of a collision, not just a possibility of a collision. Just as all hash algorithms which take something and generate a shorter "hash" or "checksum" will always have collisions. Without exception and as an absolute 100% certainty. There is no way to avoid this mathematical certainty.
However, whether the absolute and unmitigatable certainty of a collision is of any import or not is an entirely different matter. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski >Sent: Friday, 24 November, 2017 11:21 >To: SQLite mailing list >Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table >creation [Bug] > >Obviously, this is a design time factor, but, in my applications, I >always >use integer IDs if I'm ever going to bring the info that row contains >(And >other relevant info) to the UI. I have never had a solid reason to >use >GUIDs or UUIDs or whatever. > >Any time I'm adding something to a listbox, combo box, or whatever it >is, >that element of that lists Object (RE: tStringList) gets the ID that >is in >the database. Using a UID would not work, unless I spend CPU cycles >converting a 32 character string to bytes, then to 128-bit numbers. >But >then, my compiler is 32-bit only, but can emulate 64-bit numbers. > >I can see the reasoning why a UUID is appealing, but, an ID is an ID. >It >doesn't matter what it is. When you use INTEGER PRIMARY KEY, you get >from >1 to 2^64-1 numbers to play with at LEAST. I don't know if SQLite >will go >into 128 or 256bit integers. With UUID, you're looking at a chance >of >collision. Small, yes. But its there. With INTEGER PRIMARY KEY, >you're >going up by one each time. Since I will never care what that ID is, >as a >developer or as a user, Integer IDs are perfect. > > >On Fri, Nov 24, 2017 at 12:33 PM, Richard Damon <Richard@damon- >family.org> >wrote: > >> On 11/24/17 3:51 AM, R Smith wrote: >> >>> >>> On 2017/11/24 5:23 AM, Peter Halasz wrote: >>> >>>> As for whether I need to use AUTOINCREMENT, it seemed like a good >idea to >>>> avoid rowid reuse, but I can avoid using it for the sake of >optimization, >>>> so probably will. >>>> >>> >>> I agree with Keith and has many times mentioned this before >(apologies to >>> others for the déjà vu). You as the programmer / designer should >always be >>> in control of how and why a new ID is assigned. >>> >>> I would disagree here, in many cases the ID (rowid) is a purely >internal >> attribute with the purpose of accessing the data. It may have no >problem >> domain significance. If the primary key's purpose is purely data >access, >> then letting the data access layer handle it makes sense. The one >case >> where it makes sense for the programmer / designer to take control >of the >> PK is if the domain naturally has an identifier that would be >suitable for >> the key (an reasonable sized integer that is naturally unique), The >> designer should also be fairly certain that it will remain so. >> >>> It feels like a saving to let the DB engine do it for you, but it >isn't >>> really. What you save in a bit of code that decides the new ID >before-hand >>> (which can be as simple as SELECT MAX(id)+1 FROM t) you typically >have to >>> catch up afterward in code to do the usual get-LastInsertedID and >then pop >>> that in wherever stuff wants to link to the new item. It's a zero- >sum gain >>> really. >>> >> The big issues with this method is if two processes both try to >create a >> new item at the same time, one of them is going to get an error and >have to >> redo its work. If you start by creating the record with an >autoincrement >> id, and then getting the ID used, then you remove the need to >handle the >> error on the simultaneous creation. >> >>> >>> I'm not even a big fan of Integer IDs, I think codes / UUIDs are >best, >>> but obviously the speed/size gain with an integer key (especially >INTEGER >>> PRIMARY KEY row-id alias in SQLite) can't be ignored. >>> >>> Making a UUID or other 'big' key the primary access key will >increase the >> cost of looking up a record for ALL purposes. For small tables, it >might >> not be measurable, but small tables are less apt to need that sort >of PK >> either. It can make sense to use a key like that as an External Key >to >> describe the record to the outside world. Perhaps if the ONLY >accesses to a >> table are via this 'big' key, and very rarely by some other >key/field, >> making the big key the primary key would make sense. >> >>> Disclaimer: This is only my opinion, well, I'm not completely >alone in >>> it, but it is still an opinion and not a general SQL prescription. >>> >> >> -- >> Richard Damon >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
