Universally Unique Identifiers (UUIDs), also known as Globally Unique Identifiers (GUIDs), can be used to uniquely identify
rows in a table. The values are generated such that a value produced on one computer will not match that produced on another.
They can therefore be used as keys in replication and synchronization environments.
Using UUID values as primary keys has some tradeoffs when you compare them with using GLOBAL AUTOINCREMENT values. For example:
- UUIDs can be easier to set up than GLOBAL AUTOINCREMENT, since there is no need to assign each remote database a unique database
ID. There is also no need to consider the number of databases in the system or the number of rows in individual tables. The
Extraction utility (dbxtract) can be used to deal with the assignment of database IDs. This isn't usually a concern for GLOBAL
AUTOINCREMENT if the BIGINT data type is used, but it needs to be considered for smaller data types.
- UUID values are considerably larger than those required for GLOBAL AUTOINCREMENT, and will require more table space in both
primary and foreign tables. Indexes on these columns will also be less efficient when UUIDs are used. In short, GLOBAL AUTOINCREMENT
is likely to perform better.
- UUIDs have no implicit ordering. For example, if A and B are UUID values, A > B does not imply that A was generated after
B, even when A and B were generated on the same computer. If you require this behavior, an additional column and index may
be necessary.
See also
