7 ms·
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
by skrebbel 11y ago
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
- clavalle 11y agoThere is no way to enforce data integrity between related tables by virtue of the data model for one thing.
- jtmarmon 11y ago?? foreign keys?
- billybugmaster 11y agoisn't that what the fk are for? food_uuid for example. I concede I wouldn't call it uuid but I don't see a problem keeping the data straight.
- gcr 11y agoI don't get this argument. Foreign key constraints are part of the schema. What should the primary key be if not a UUID?
- nickpeterson 11y agoFirst there is the question of whether one should have surrogate keys at all, some oppose surrogate keys because: They make tables more difficult to reason about. They encourage structures that involve large amounts of joins. Others embrace surrogate keys because: They make joins between tables easier (usually one field rather than a compound key where one might accidentally not include all the join predicates). They save space since an integer is almost always smaller than a compound key. So if you are in the camp that favors surrogate keys, then consider these points: Some quick downsides for UUIDs: If your table has a clustered index, UUIDs are generally not created in a regular order, and so you'll be constantly inserting into a part of your index rather than the end. Some databases allow sequential UUID generation to help mitigate this. UUIDs are usually 16 bytes, but an int is usually 1-8 bytes. Also keep in mind you pay this price multiple times since the primary key (generally) becomes the foreign key in other tables. Some upsides for UUIDs: If you have a process that has no master, being able to tie disparate things together with a shared UUID is very useful sometimes. Often developers use UUIDs/GUIDs because: Their app wants to perform a 'Create' operation and they want to generate the key in advance. This can usually be mitigated by creating a routine that generates a range of reserved surrogate integer keys, and gives a starting value to the application layer. Then the application layer can use those values with knowledge they won't already exist. For instance, the app layer say, "I'm going to make 100 customers", and the response comes back "10232". Meaning that 10132-10232 is reserved for that process to use for customer primary keys. This allows larger (chunkier) requests which can be interrelated without constantly making requests to the Database layer. One could extent this to be a true cache for entity reserved numbers that gets dolled out as needed by the app layer.
- mvitorino 11y agoThe problem is that most uuids are generated in a way that, when sorted alphabetically as strings, would have a random order. Example: t0: 7458e3a9-716b-4352-b2e4-b5b67d0c089b t1: 4d8d753c-1777-439d-8725-b093b1bd8430 Using this as a PK in any relational database will mean the rows are stored in the clustered index order, which causes extreme fragmentation because the db engine constantly has to find "holes" in the data pages to insert more recent records instead of adding those at the bottom of the table as would happen with any continuously increasing key.
- gnaritas 11y agoThat would be true except for that fact that database designers know this, and offer a way to generate sequential GUID's; so it's actually not a problem for that reason. The only real downside is performance, an int key performs better.
- dhd415 11y agoEvery sequential GUID generation feature provides guarantees only per session. In other words, when you bring your database down to upgrade or patch it and then bring it back up, your GUID sequence starts over again at some arbitrary value which is likely to overlap with your existing GUIDs.
- gnaritas 11y ago> your GUID sequence starts over again at some arbitrary value which is likely to overlap with your existing GUIDs. I don't believe that's true. You're right that they start the session over, but it'll still be a fresh sequence that doesn't overlap anything generated in the past.
- jsmthrowaway 11y ago> an int key performs better. A UUID is a 128-bit integer. That people do not store, generate, or interact with them that way is the bug. I'd kill for a 128-bit architecture so a UUID compare would be a single instruction, and it's too bad consensus is that we don't really need it.
- kawera 11y agoSome good answers here: http://stackoverflow.com/questions/45399/advantages-and-disadvantages-of-guid-uuid-database-keys http://stackoverflow.com/questions/45399/advantages-and-disa...
- ww520 11y agoUUID as PK is perfectly fine; however, as with any design decision it really depends on your needs and weighing alternatives. Some popular ways to do PK: natural key, sequentially generated key, and UUID. Personally I would prefer natural key if I can find an immutable natural key for the table. However, natural keys are hard to find. The food example in this case doesn't have natural key. Also if the natural key requires a compound key, it's just not worth the pain. Sequential auto generated key is good when you need to hand it off to users, like order id or ticket number. It's short and simple and it's auto generated. The downsize is when you migrate databases, you need to seed the new database carefully or it would start from beginning again. Also in record creation, you need to do extra read to get back the newly generated key. UUID is mostly worry free. It can be generated anywhere and doesn't need to be in the database. For a setup with distributed databases, I would use UUID just to have global uniqueness. For an offline app, I would use UUID to create the data records locally and later sync them with the main database. UUID is good when it's used internally and not exposed to the users.
- jon-wood 11y agoSequential IDs can also leak business information when exposed to customers such as how many orders your taking - an interested party can place an order at 10am and another at 11am. Once you've done that you can compare the IDs and know how many orders were taken that hour.
- irl_zebra 11y agoWhy should UUIDs not be exposed to users? Because of the their unwieldy look, or because of security concerns? I ask because I have an app where previously I was accessing a particular data point via pk, and the user saw the pk in the url bar. But it could expose user/site data as any user could access that pk, or just guess at the next sequentially generated pk. I switched to uuid and now it's a little ugly in the url bar, but no data is exposed.
- ww520 11y agoI meant the key handed off to the user for human interaction. As a user I would prefer order number 7356 than 7458e3a9-716b-4352-b2e4-b5b67d0c089b. If there's no human interaction with the key, UUID is perfectly fine.
- Scirra_Tom 11y agoCame across an interesting case against using UUIDs the other day, in the UK it's a legal requirement that VAT invoices are sequential: http://www.hmrc.gov.uk/manuals/vatrecmanual/vatrec5020.htm http://www.hmrc.gov.uk/manuals/vatrecmanual/vatrec5020.htm Using random UUIDs as an invoice number wouldn't cut it, you'd have to have a sequential ID in some form. You could of course have both but that's adding unnecessary complexity. Another example, if you are designing a website and lets say give each blog post a UUID, a URL of: website.com/blog/my-post-34 Looks better than website.com/blog/my-post-d173affb-6bb8-4435-bef1-4e29409dff4c Unique sequential Id's also give some clues about the relative age of rows (eg ID 100 is older than ID 50). This doesn't come up often but can sometimes be useful. Regardless, I always find using a unique int ID for pretty much every record makes your job 10x easier and I fail to see why you would ever want to do anything different. If you want to 'hide' the ID in special cases (eg order numbers) better to generate a unique random int ID.
- xrstf 11y agoSequential order IDs are also mandatory in Germany. At least I remember that we had to make sure in one project that the order IDs had no gaps and were sequential.