
Ask HN: GUID, int, or both for primary key? - Fsp2WFuH
If both I&#x27;d just use int as primary key, and short guid as unique indexed key.<p>I like being able to get last n records if my primary key is int. At the same time I don&#x27;t want to show to the public my auto incrementing int, I&#x27;d rather do a form of short guid. Reason is to prevent someone form randomly incrementing to next record or something similar. Most of my tables also have a created datestamp. Not sure, any thoughts on this?
======
coreyp_1
You actually answered your own question, which I'm glad for because that means
that you are thinking about security.

Use numeric IDs internally (for foreign keys, etc.), and GUIDs for anything
public-facing. NEVER expose the internal IDs to the end user, but, conversely,
only use IDs internally in the database. As long as you are consistent, then
it will never be a problem.

[edit: added this sentence] The ID will be the primary key, but the GUID can
be a key as well, but it does not have to be part of the primary key.

As a side note, it's not just about a person being able to increment to the
next record, but that it exposes information about your system. If a person
has just joined your website, and they know that their ID is 7, then they know
that there is nobody else on the site (and that's potentially catostrophic).
If their order # is 5, then they know that nobody else is buying from you.
Essentially, exposing IDs is, in essence, an information leak.

------
eesmith
Various discussion about something similar at
[https://news.ycombinator.com/item?id=16946557](https://news.ycombinator.com/item?id=16946557)
. This concerned the essay "User IDs probably shouldn't be passed around as
ints" at
[https://rachelbythebay.com/w/2018/04/27/uid/](https://rachelbythebay.com/w/2018/04/27/uid/)
.

The author argues that auto incrementing ints have additional failure modes
that sparse integers don't have, even if those sequential values aren't user-
facing.

If they have a created datestamp then if you index them then it's easy to get
last-n.

------
daleholborow
There are additional arguments against using ints as ids in the database, if
you ever plan to shard etc, as in "you'd still have to do a lot of work to
migration data from an integer-key-based-system to something distributed".
Various articles describe this. Keep in mind that many RDBMS systems now
support some concept of a "sequential guid" \- but keep in mind that they all
store these in different ways. Plug: here's some code I pulled together, with
links to the reference material, that will generate sequential guids for
various databases:

[https://github.com/daleholborow/iayos.sequentialguid](https://github.com/daleholborow/iayos.sequentialguid)

Speaking from personal experience, in theory I would always choose a
(sequential)GUID as PK.. BUT... they ARE a pain in the bollocks to work with
when you are developing and writing sql by hand to query data. E.g.

a) select * from Thingies where Id = 5

vs

b) select * from Thingies where Id = '12331-432Aafds-32424-df'

------
jstewartmobile
If you do GUID PK or clustered index in an RDBMS, probably want to turn it
into a monotonic GUID.

Many DBs order the table by PK. If the GUID value moves backward, that's a
slow insert into the middle instead of a fast append to the end.

[https://dba.stackexchange.com/a/17212](https://dba.stackexchange.com/a/17212)

