Definition
UUIDs are 128-bit numbers that are unique with a high probability. These numbers you can use as id in databases. Most databases like postgres, mySql, SQLServer, Oracle, Access support that datatype.Index for Data
Every record should have an identity to quickly refer to it. The database-field where an ID identifies a record uniquely is call primary key. If there is a reference in another database-table to a record, that field is called foreign key. An ID must be unique and not null. A foreign key may be null, if there is no reference to another record and it does not need to be unique, because there could be more than one object related to one object (1:n relation).There are several ways to create a unique id:
Auto-Increment-Fields
A long time auto-increment-fields or a combination of multiple fields was used as primary key in databases - with many disadvantages. If you use auto-increment-fields and want to combine data from another datasource in the same table you might run in the problem that ids are not unique, because every datasource begins to count from 1. In that case you need to make IDs unique again. Therefore all colliding ids need to get a new id. This is very error-prone.Indexes with multiple fields
Another option is to build a primary key with multiple fields that are not unique themselves, but a combination might be. Assume you want to store data about persons. If you use the firstname and the surname it might not be enough for the record to be unique. You will need more fields. Perhaps the date of birth. But who tells you that there will not be a person with the same name and birthdate? So how many fields do you need to be sure to be unique? Perhaps all fields of the table. But then it's no primary key you can use in another table to reference.If you change one of those attributes the primary key is consisting of, you need to update every appearance in other tables. As keyword remember what happens if a person gets married.
To reference you want to have an attribute that does not change along with the referenced data.
UUIDs
If you use UUIDs then you will be very sure that the randomly created values won't reoccur again anytime. And they are independent of the data you want to use with. The only drawback is that UUIDs are not created in an ascending order. You will not be able to determine the latest appended records of a table by looking at the UUID. The word "latest" in this context suggests that something with time happens. Why not creating an additional field with a timestamp of the creation date and time?But also UUIDs have drawbacks. Think about a list of all cities in a single database-table. If you want to combine data of two tables with cities, you might run into having duplicate entries with different uuids, if in the second table the same city was inserted before. But in that case it is less error-prone to substitute one city-uuid by the other one. At any time the data keeps consistent and without conflicts.
Who should create the uuid - application or database?
In case of auto-increment fields and indexes with multiple fields it is obvious that the database creates that data. But how about uuids?If the database creates the uuid you might get into trouble if you want to replace an existing uuid by another, if the database prevents that. It is also more difficult to get the id after inserting a record into the table. In that case you need to re-read the currently inserted record from database or evaluate the parameter returned by the insert-operation on the database.
It should be easier to let your application create and manage uuids, so you pass it directly to any insert-operation as parameter and can also use it as a return-value if the insertion was successfully done.