Full Support for GUID (or UUID)

A GUID (Globally Unique Identifier) is a 128-bit quantity generated by an algorithm that nearly guarantees to make it unique. A GUID is displayed as a sequence of hexadecimal digits, in groups separated by hyphens. For example: {C0E61A92-BE06-4557-AABA-36AB263E0457}.

A GUID has a data type uniqueidentifier in MS SQL, RAW(16) in Oracle, and uuid in PostgreSQL. VDF treats GUIDs as strings, making searching easy.

Mertech has provided GUID, also referred to as UUID (Universally Unique Identifier), support for several versions, but, with Flex2SQL v12.1 we have added something new. Now you can use a GUID as your primary key. This new feature gives developers the ability to create GUID columns with server- or client-side GUID generation and to use them seamlessly with VDF applications. The generated key is passed back to the VDF application so you can use it in a re-read to retrieve the record.

Why Use Dynamic Primary Keys

There are several reasons you might want to use dynamic primary keys:

• It makes it easier to distribute a database across multiple servers.

• You don’t have to worry about collisions if you need to merge rows from multiple versions of the same table.

• Keys can be generated anywhere without a round trip to the server.

• Replication often requires a GUID column.
  
  
  

FAQ

Q: Is this feature supported on RECNUM tables?
A: RECNUM based tables use the RECNUM column as a primary key, therefore this feature isn't applicable in RECNUM based tables.

Q: Can a Universally Unique Identifier (UUID) column also be sequential?
A: There is a way to create sequential UUIDs. There are certain advantages of non-sequential UUIDs that are given up if this is done, but it is possible. The actual implementation of a sequential UUID is beyond the scope of this document.

Q: What is the advantage of using a UUID over a standard sequence based primary key?
A: A sequence based primary key could easily collide with data from another primary key column in another table if the tables need to be merged. From a security standpoint, a globally unique identifier has a very low collision rate, which equates to almost no possibility of guessing a UUID. After generating one billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%.

Limitations

Dynamic primary keys are supported on MS SQL, Oracle, and PostgreSQL (version 8.2 and above). All supported platforms can only make use of a dynamic primary keys on tables that do not use Insert Triggers. Insert Triggers prevent us from returning the newly created primary key. MySQL does not have the needed interface to support dynamic primary keys.

Note: PostgreSQL requires installation of the OSSP UUID library to generate UUIDs. When you launch the Flex2SQL Migration Utility a message displays if the uuid-ossp module is missing. This module can be installed by running uuid-ossp.sql, which can be found in the (Postgres installation directory)\share\contrib.