Which is better? I know that autoincrement fields allows you to get the last inserted id which is helpful when dealing with multiple tables, but other than that I'm not sure which I should use to make the database more secure/robust.
qazplm114477 14 Junior Poster
griswolf 304 Veteran Poster
What is this 'better'? You really need to specify more completely what you need before you can decide what will work better for your particular needs.
- size: Auto increment works for up to about 2*10^19 items. UUID works for up to about 10^38 items.
- unique key: auto_increment guarantees unique (and fails if you run out of integers) UUID is intended to be unique (even across non-communicating machines) but I doubt there is an actual guarantee: You would need to work harder to avoid extremely rare possible conflict.
- recent key access: You can easily get the most recent auto-incremented value (per connection) without doing a select; but you need to do something more difficult to get the most recent UUID
- speed: I have no data, but on the face of it, auto increment should be quicker than UUID()
- externally generated key: Allows easy programmatic access to the key (whatever it is) as well as a 'perfect' key size and type, but requires thoughtful coding for multiple threads.
- limitation: only one auto increment column per table, but since UUID() generates text, there is no limit, similar for externally generated key
- efficiency: An index on an int type may have different behavior than an index on a text type
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.