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.
griswolf 304
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
