# UUID - separate table? **URL:** \ **Category:** Databases\ **Created:** [December 1, 2013, 10:57am UTC](https://forum.xojo.com/t/uuid-separate-table/12728 "2013-12-01T10:57:01Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Alex\_von\_Siebenthal](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/c67d28/32.png) [@Alex\_von\_Siebenthal](https://forum.xojo.com/u/Alex_von_Siebenthal)\ **Post date:** [December 1, 2013, 10:57am UTC](https://forum.xojo.com/t/uuid-separate-table/12728/1 "2013-12-01T10:57:01Z")
For my upcoming projects I want to get rid of the auto-increment primary keys and use UUIDs. I know that using a decent UUID generation algorithm will result in a very low collision probability. However, might it make sense to have an additional table and track all used UUIDs - and check against those when adding a new record? ---
**Author:** ![Peter\_Fargo](https://forum.xojo.com/user_avatar/forum.xojo.com/peter_fargo/32/977_2.png) [@Peter\_Fargo](https://forum.xojo.com/u/Peter_Fargo)\ **Post date:** [December 1, 2013, 1:59pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/2 "2013-12-01T13:59:38Z")
If your UUID field is set to unique the database will error which you can trap and generate a new UUID. ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [December 1, 2013, 2:01pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/3 "2013-12-01T14:01:19Z")
I haven’t tried this, but that seems like an excellent idea as long as you “check” your new UUID by attempting to add it to the table, then checking for an error. As long as there is none, you know you’re safe to use it for your new record. ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [December 1, 2013, 2:02pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/4 "2013-12-01T14:02:54Z")
> [@50121:@Peter Fargo](#): > > If your UUID field is set to unique the database will error which you can trap and generate a new UUID. I’m pretty sure he wants an ID that is unique across all records in every table. ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [December 1, 2013, 5:25pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/5 "2013-12-01T17:25:54Z")
The more tables you have the slower it’s going to be to check that it truly is UNIQUE across all tables unless you keep one huge master table full of just UUID’s Just curious why you want to do this ? ---
**Author:** ![Alex\_von\_Siebenthal](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/c67d28/32.png) [@Alex\_von\_Siebenthal](https://forum.xojo.com/u/Alex_von_Siebenthal)\ **Post date:** [December 1, 2013, 5:34pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/6 "2013-12-01T17:34:16Z")
Accidentally clicked on solved… sorry! can’t undo!? > [@50139:@Norman Palardy](#): > > unless you keep one huge master table full of just UUID’s That was the plan. > [@50139:@Norman Palardy](#): > > Just curious why you want to do this ? To avoid collisions? ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [December 1, 2013, 5:52pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/7 "2013-12-01T17:52:37Z")
Collisions in what ? IN a single DB ? Synching many db’s together ? UUIDS in a single DB are unnecessary if you design it properly ---
**Author:** ![Alex\_von\_Siebenthal](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/c67d28/32.png) [@Alex\_von\_Siebenthal](https://forum.xojo.com/u/Alex_von_Siebenthal)\ **Post date:** [December 1, 2013, 6:00pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/8 "2013-12-01T18:00:28Z")
It’s also about syncing across devices, merging records etc. ---
**Author:** ![Oliver\_Osswald](https://forum.xojo.com/user_avatar/forum.xojo.com/oliver_osswald/32/6972_2.png) [@Oliver\_Osswald](https://forum.xojo.com/u/Oliver_Osswald)\ **Post date:** [December 1, 2013, 6:24pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/9 "2013-12-01T18:24:43Z")
From a [discussion on the old forum](http://forums.realsoftware.com/viewtopic.php?f=1&t=44203&start=13) : > [@](#): > > Version 4 UUID (random) are not 100% unique. However, if constructed according to the documentation, then a collision is highly unlikely. “… after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%. The probability of one duplicate would be about 50% if every person on earth owns 600 million UUIDs.” ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [December 1, 2013, 8:38pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/10 "2013-12-01T20:38:07Z")
> [@50152:@Alex von Siebenthal](#): > > It’s also about syncing across devices, merging records etc. That is an entirely different issue - I’ve done that before BUT the merging & synching has more to do with business rules than anything. The rest is fairly mechanical. Still doesn’t require UUID’s and a single UUID table that you constantly try to add new ones to will slow things down when you get to any reasonably large size db ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [December 1, 2013, 8:38pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/11 "2013-12-01T20:38:52Z")
> [@50143:@Alex von Siebenthal](#): > > Accidentally clicked on solved… sorry! can’t undo!? I did 😛 ---
**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\ **Post date:** [December 1, 2013, 9:39pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/12 "2013-12-01T21:39:56Z")
Maybe this should be a separate conversation, but I wonder about generating UUID’s. I know there is an MBS plugin for it (isn’t there always?), and there are specific algorithms, but it seems to me that I’d just take some random bytes, add Microseconds as string, then generate a SHA512 hash on it which I’d then hex-encode. With 1.3e154 possible hashes, I’d be pretty comfortable that there would be no collisions, at least not in my lifetime. Something like this: ```auto Function UUID() As String static myRandom as new Random dim bytes as new MemoryBlock( 64 ) for i as integer = 0 to 63 bytes.Byte( i ) = myRandom.InRange( 0, 255 ) next i dim r as string = bytes + str( Microseconds ) r = Crypto.SHA512( r ) r = EncodeHex( r ) return r End Function ``` If you want something shorter, you can use SHA256 (1.16e77 combinations) or SHA1 (1.46e48 combinations). Issues? ---
**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\ **Post date:** [December 1, 2013, 9:52pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/13 "2013-12-01T21:52:51Z")
If you’re concerned about syncing data, wouldn’t you have to worry about syncing the UUIDs, too? I mean, you could conceivably have a collision between databases. So a “master” UUID table in the database still doesn’t cut it. You’d have to implement some common mechanism that all databases query to get a UUID. ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [December 1, 2013, 10:24pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/14 "2013-12-01T22:24:26Z")
> [@50198:@Tim Hare](#): > > If you’re concerned about syncing data, wouldn’t you have to worry about syncing the UUIDs, too? I mean, you could conceivably have a collision between databases. So a “master” UUID table in the database still doesn’t cut it. You’d have to implement some common mechanism that all databases query to get a UUID. Right - thats why I say that UUID’s aren’t really required to sync DB’s. It has more to do with designing the DB with syncing in mind so you can match up records that are “the same” and then deciding what to do if records in both db’s (or more than one) have been altered. That requires good selection of primary keys so you CAN identify things properly and definitively match rows. For instance if you’re synching customer data then a customer ID makes sense. Now, can each end user of this database add new customers ? If so then how you sync will have to resolve the possibility that I added a customer with ID 10 and someone else also may have added a different customer with that ID. But HOW this should work really is a business driven not technology driven. ---
**Author:** ![Peter\_Truskier](https://forum.xojo.com/user_avatar/forum.xojo.com/peter_truskier/32/243_2.png) [@Peter\_Truskier](https://forum.xojo.com/u/Peter_Truskier)\ **Post date:** [December 2, 2013, 1:54am UTC](https://forum.xojo.com/t/uuid-separate-table/12728/15 "2013-12-02T01:54:10Z")
> [@50193:@Kem Tekinay](#): > > I know there is an MBS plugin for it Also an Einhugur function… ---
**Author:** ![LiamP](https://forum.xojo.com/user_avatar/forum.xojo.com/liamp/32/543_2.png) [@LiamP](https://forum.xojo.com/u/LiamP)\ **Post date:** [March 12, 2017, 8:26pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/16 "2017-03-12T20:26:14Z")
I haven’t understood why yet, but the Adobe Lightroom catalog (a SQLite database) uses a lot of UUIDs. Since Lightroom is single user and doesn’t sync with other catalogs, I don’t know why they use them… ---
**Author:** ![scott\_boss](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/c5a1d2/32.png) [@scott\_boss](https://forum.xojo.com/u/scott_boss)\ **Post date:** [March 12, 2017, 8:51pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/17 "2017-03-12T20:51:04Z")
> [@320144:@william plunkett](#): > > I haven’t understood why yet, but the Adobe Lightroom catalog (a SQLite database) uses a lot of UUIDs. Since Lightroom is single user and doesn’t sync with other catalogs, I don’t know why they use them… most apps that use them use it guarantee unique IDs. and there is no limit on IDs (integers have a limit based on the size of the int). ---
**Author:** ![Antonio\_Rinaldi](https://forum.xojo.com/user_avatar/forum.xojo.com/antonio_rinaldi/32/331_2.png) [@Antonio\_Rinaldi](https://forum.xojo.com/u/Antonio_Rinaldi)\ **Post date:** [March 13, 2017, 8:08am UTC](https://forum.xojo.com/t/uuid-separate-table/12728/18 "2017-03-13T08:08:03Z")
UUID help a lot for db sync, but you have also to check for other constrains (logic, time etc, Depends on what are the rules of you data) I use standard OS functions to generate them with just a “postproduction” to let them be consistent between platforms. The probability of a collision is very little (generated billions and never got any problem) and, more important, you should care of it at table level. Do you care if, using integer id, you have a order with id =1 and a customer with id=1 ? ---
**Author:** ![LiamP](https://forum.xojo.com/user_avatar/forum.xojo.com/liamp/32/543_2.png) [@LiamP](https://forum.xojo.com/u/LiamP)\ **Post date:** [March 13, 2017, 12:47pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/19 "2017-03-13T12:47:48Z")
The example I used was the Adobe Lightroom Catalog. I access it with my Xojo app to retrieve data about the image. I had to hack/reverse engineer the LR Schema. It uses UUID in several places and I have no Idea why. LR catalogs don’t sync with other catalogs and with the exception of the Mobile LR app on an iPad, doesn’t interface with ANYTHING else, at least that’s documented. The UUIDs may relate to the mobile app but I don’t know that. I never looked at the LR Schema prior to there being the mobile app. ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [October 29, 2020, 4:58pm UTC](https://forum.xojo.com/t/uuid-separate-table/12728/20 "2020-10-29T16:58:04Z")