4

For a database. Should I use an UUID or an integer for the primary key?

I will never exceed 2^32 in amount of rows, so an integer should be more than plenty.

I would prefer to use an integer, as I find it easier to work with, especially when using different languages.

Question: What is the advantage of UUIDs over integers?

KaareZ
  • 151
  • 1
  • 7
  • 3
    Most probably a dupe of http://stackoverflow.com/questions/45399/advantages-and-disadvantages-of-guid-uuid-database-keys –  Oct 02 '15 at 08:24
  • The post [primary-keys-ids-versus-guids/](https://blog.codinghorror.com/primary-keys-ids-versus-guids/) and the question [What's your opinion on using UUIDs as database row identifiers](https://stackoverflow.com/questions/5949/) are also worth reading. – surfmuggle Apr 08 '22 at 09:59

3 Answers3

7

The intent of UUIDs is to enable distributed systems to uniquely identify information without significant central coordination. (Wikipedia)

UUIDs are merely integers that are picked at random by nodes that cannot necessarily communicate with each other, so they are only very, very likely to be unique rather than guaranteed. If you're programming a NON-distributed system, there is no advantage.

Kilian Foth
  • 107,706
  • 45
  • 295
  • 310
  • 3
    Actually, you are describing one of a number of UUID types. Other types of UUIDs are not picked at random; e.g. types 1 and 2. Even the hash-based UUID schemes are not picked "at random" ... strictly speaking. – Stephen C Oct 02 '15 at 12:01
4

In addition to what Kilian Foth said, an advantage to UUIDs can be that they increase the address space and may be harder to guess.

Example: If you expose a REST API that contains the id of an entity in the path, a hacker might guess that if

GET http://mysupersensitive.data/supersecret/42 works,

GET http://mysupersensitive.data/supersecret/41 might work as well.

When it is UUIDs, this becomes much harder.

EDIT: The comment by Jules is right of course, this in itself must not be regarded as a security mechanism.

ftr
  • 1,601
  • 1
  • 14
  • 19
  • Harder, but still not a good idea to rely on this for protection, as valid urls may be leaked in a way that allows a hacker to find them (e.g. by viewing an authorized user's browser history). – Jules Oct 02 '15 at 18:58
-1

Use a GUID/UUID

Honestly the number of times I heard "we will never run out of integers" only for whatever system to quickly run out of integers.

UUIDs offer significant advantages when you come to archiving data, replication and having code able to generate new Ids without writing to the database

Ewan
  • 70,664
  • 5
  • 76
  • 161
  • 1
    Honestly I don't expect my game to be played by more than ~half the world :) – KaareZ Oct 02 '15 at 09:40
  • That's the trap, thinking that you know all the possible ways to use up a number. "lets run a load test", "numbers between 1m and 2m are test accounts", "start the seed at 100000 so all the account numbers will be the same length", "it creates an account because it needs the id, but then the user doesn't complete the form", "the sign up form was spammed!!" etc etc etc – Ewan Oct 02 '15 at 09:54
  • 1
    one word: YAGNI. and even in that case, it's far easier to replace an integer with a BIGINT. UUIDs have advantages in well-defined cases. everywhere else are a very noticeable overhead. – Javier Oct 02 '15 at 15:33
  • GUIDs are the simpler case because you don't have to hit the DB for an Id. using ints because of a slightly slower insert on large tables? YAGNI. and I repeat, in this case, in my real life experience, __multiple times__, people says YAGNI and __THEN NEED IT__ – Ewan Oct 02 '15 at 16:15
  • @Ewan - If you take it literally (which we tend to do in this field) and use it often enough, you'll eventually need it, but so what? All the time you spend implementing all those edge cases could have been spent on the current problem. – JeffO Oct 02 '15 at 20:00
  • It's actualy less lines of code than using ints and testing, replication, archive and backup arnt edge cases. You ARE going to need it – Ewan Oct 03 '15 at 08:37