Servoy TIP: How and Why To Implement UUIDs As Your Primary Key

When setting up your database tables (if you create them in Servoy) - you know that Servoy automatically creates an integer unique record id that you can use when you relate data to/from another table. It's called a primary key. If you're working with datasources and tables that were created elsewhere (or you have a DBA that creates them for you) - they probably have a record id column that set to auto-number (also called an identity column) that auto-increments when a new record is created.

In most cases, an auto-incrementing integer primary key will be just fine.

However, there are some circumstances where that simple system will cause you huge headaches. Here's just a couple of them (if you have others - leave a comment):

• If you have a runtime solution that you want to synch back to a "main" system
• If you want to use database-level replication to have multiple instances of the same data
• If you have a SaaS solution where you have multiple customers entering data

All of these situations demand that you have something other than a simple auto-incrementing integer as your primary key. Enter the UUID. What exactly is a UUID? Here's the Wikipedia definition:

A UUID is a 16-byte (128-bit) number. The number of theoretically possible UUIDs is therefore about 3 × 10³⁸. In its canonical form, a UUID consists of 32 hexadecimal digits, displayed in 5 groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 digits and 4 hyphens). 

For example:
550e8400-e29b-41d4-a716-446655440000

Luckily, Servoy makes it simple to create these types of columns. It's easiest to implement UUIDs when you're first starting off your solution - but it is possible to change over an existing solution (although it can be a ton of work).


If you're starting from scratch here's how to set up your primary key as a UUID:

Create a new table - I'm using a table called "customer" as an example. You'll see that Servoy automatically creates a column called "customer_key" that is defined as an auto-increment, integer, primary key:

The first thing you want to do is to change the "Type" to TEXT and set the length to 50;
Then from the "Sequence Type" choose "uuid generator";
Finally, make sure you check the UUID checkbox in the "Flags" section (Servoy 6 does this for you automatically):

Now you can go ahead and add whatever other columns you want - and save the table. When you go to create a related table (in this case I'm creating a new table called "Invoice") - you want to create a TEXT foreign key (to relate the two together) - and you want to also make sure you set the UUID flag on that field as well:

Now you can go through and build your relationships, and everything as you normally would - easy!

Now, what if you want to convert a solution that exists - that's using the "traditional" integer keys? My suggestion: don't.

Seriously.

It's a lot of work and there will be a huge amount of testing and data stuff to do to populate all the primary keys and foreign keys; go though all the references to the ID field in all your methods, valuelists and relations; etc., etc.

However, if you must - here's a brief overview on how to do it.

• Create a new TEXT column (length 50) in all your tables and make sure you check the UUID flag. I usually call mine something like TableName_UUID (e.g. customer_uuid, invoice_uuid, etc.);
• For each of your related tables - create a new TEXT column for each related key and check the UUID flag. (You might need a few of these columns - for example in an invoice line item that is linked to the invoice and to the product - you'll need two columns);
• In all your tables - populate the new UUID value. The easiest way is to use a form function like this:

function setPrimaryUUID() {
controller.loadAllRecords();
var fs = databaseManager.getFoundSetUpdater(foundset);
while (fsUpdater.next()) {
fsUpdater.setColumn('customer_uuid', application.getUUID());
}
fsUpdater.performUpdate();
}

• Once all those UUID values are set - you need to set all the foreign keys in all your related tables. Now you could use a function like the above and substitute the getUUID() function with the related value (e.g. myRelation.customer_uuid) - or you can just use good old-fashioned SQL to do the update on the backend directly (that's what I usually do). My SQL statement would look something like this:

UPDATE invoice SET invoice.customer_uuid = customer.customer_uuid WHERE invoice.customer_id = customer.customer_id

• Once that's all done - you want to EXPORT A COPY OF YOUR ENTIRE SOLUTION BEFORE YOU CONTINUE!
  
• The easiest way to change all the other references in your solution - is to just search for all occurrences of your old, integer field - using the built-in search tool. Click the little "flashlight" icon and you'll see the search dialog:

• After the search -you'll see all the places that reference that "customer_id" field (in this example). You can then just double-click each object in the list to make the change. You'll have to do this for every primary and foreign key field you create! (told you it was a lot of work!)
  
• Once you've made all your changes, then you need to thoroughly test your solution - because chances are some stuff will break.

As I said, it's a lot easier to implement UUID primary keys when you're first building your solution (same goes with internationalization!).

Do you already use UUIDs as primary keys in your solutions - or just the integer keys? Drop a comment below to chime in!