Menu
I've been investigating the use of GUIDs as primary keys in databases. So far, the pros seem to outweigh the cons. However, I see one point where GUIDs may not be what I want.
In my application, users should be able to identify objects based on a user-friendly ID. So, for example, if they want to get a specific product without typing in the full name, they can use the product's ID. GUIDs aren't easy to remember for something like that.
The solution I've been thinking about is to use both a GUID and an auto-incrementing integer. The GUID would be the row's primary key, while the auto-incrementing integer would be an index used by the application's filtering functions. All SQL SELECT, UPDATE, DELETE statements would use the GUID, however.
The main reason I want to use GUIDs is to prevent clashes when merging two databases. If Database #1 and Database #2 both have a Product #2, the importer script would have to change the ID and all foreign keys referring to it. With GUIDs, I only have to change the user-friendly ID in the table itself, while foreign keys would use the GUID unique to each imported record and will therefore work without modification.
So, my question is: are there any major problems (besides the size of the GUID field and easy page fragmentation) with having an auto-incrementing integer index and a GUID primary key?
246
int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid , the only advantage I found is that it is unique. In which case a Guid would be better than and int and why? From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.
As mentioned earlier, GUID values are unique across tables, databases, and servers. GUIDs can be considered as global primary keys. Local primary keys are used to uniquely identify records within a table. On the other hand, GUIDs can be used to uniquely identify records across tables, databases, and servers.
Less space. UUID always occupies 16 bytes. For Auto Increment Integer, when stored as Long format, it occupies 8 bytes. If the table itself has only a few columns, the extra primary key space overhead will become more significant.
A Primary Key just needs to be a unique value that identifies its entry from other entries, and not null. Save this answer. Show activity on this post. Primary key should be unique but not necessarily need to be auto_increment.
I always tend to use surrogate primary keys in my database. That is: those primary keys have no actual meaning in the problem domain, and thus, those primary keys are never exposed to users. (If this surrogate primary key is of type GUID or an identity, I don't care; this depends on the requirements).
If you say that users should be able to identify objects based on a user-friendly ID, then, I think that this user-friendly ID is a value that belongs to your 'problem domain'. This means, that this ID should indeed be an attribute in your table, but it should not be used as the primary key in your table.
This also allows you to easily modify the value of such an user-friendly ID (if that should be necessary), without you having to worry about modifying related foreign keys as well.
72
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
