Ask HN: GUID, int, or both for primary key? If both I'd just use int as primary key, and short guid as unique indexed key. I like being able to get last n records if my primary key is int. At the same time I don't want to show to the public my auto incrementing int, I'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? |
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.
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.
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'
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.