Todd and I talk about FileMaker Primary Keys today in the Context Podcast. Here’s the episode. Listen to it here.

And here are the highlights.

Well-Discussed Topic

The topic of FileMaker primary keys gets talked about a lot in the community, and indeed I asked about it long ago.

The Basics of FileMaker Primary Keys

Primary keys are simple to understand, but implementation takes nuance, and there is a lot to talk about. The nuances sort of come out of our understanding of primary keys.

The simplest database definition: A field (only one Field in FileMaker) that contains a value that uniquely identifies a record in the database in a table in the database. A primary key is an address, sort of. In other databases, you can have multiple fields to be defined as primary keys. In FileMaker, we only can use one, and that’s okay because of the platform we’re in. It’s not that necessary to study other platform’s use of primary keys, but it’s good to know others are out there.

The book Database Design for Mere Mortals by Michael J. Hernandez talks about primary keys, and defines it as being unique across the whole file. We as FileMaker developers can do that, but it’s not necessary.

Primary Keys NOT Supported in FileMaker

In fact, FileMaker doesn’t really have the strict support of primary keys. Primary keys are supported in other databases, so the database engine knows there’s a relationship between the primary key and the foreign key. You have to pick a primary key in other platforms; FileMaker doesn’t require them. In fact, for years, no real mention of the idea of primary keys was mentioned. Starter solutions had field names such as “Match Field”.

Ways to Generate FileMaker Primary Keys

We make a field be a FileMaker primary key by setting it up as unique and by using it in relationships. Since FileMaker 17, we’ve had default fields (updated in FileMaker 19) which identifies a field in that utility set as a primary key in the XML.

The Get(UUID) function ensures that each primary key value will be unique, even across the whole file. There’s certainly a difference between in-theory and in-practice, and we can be assured that FileMaker will do its best to ensure primary keys are unique. Todd has done tests of creating millions of records and never got a duplicate UUID.

FileMaker Primary keys can be serial number, auto enter, where FileMaker increments the next value by a number. There’s a lot of problems, namely that importing data into a new version of the app could cause issues in that the serial value doesn’t get updated so the next value would not duplicate any existing value. Set Next Serial Number script step helps in this regard, but it’s extra work.

There are custom functions that create primary keys as unique values. The math behind the calculation ensures a very low chance of getting a duplicate.

Get (UUIDNumber) again will not give you a duplicate in practice.

Natural Keys ≠ Primary Keys

Readability of the FileMaker primary key is nothing we should consider. Primary keys shouldn’t mean anything to developer or user, nor should they be visible on a layout. Let your strict relationship logic rely on the math of the UUID functions. It’s really not a value in a field; instead, it is a link.

Natural keys are unique keys that visually identify a record. Student numbers, social security numbers, etc. are natural keys. And these shouldn’t be used as primary keys. Natural keys are valuable for integrations. They shouldn’t be used for relationships. Natural keys are great, and use them. But just for visual identification.

Switching Key Types

If you already use serial numbers as primary keys, you should consider switching to using Get(UUIDNumber) as your method of creating keys. There’s an advantage: the type of field doesn’t have to change. If your last serial number is 45, and the first UUID Number is 893928392919281929 . . . there’s no issue; relationships will still work.

Another reason for switching from serial numbers is syncing. Without UUIDs, syncing will break. Records created on Device 1 and device 2 can have the same serial value in the key, and that’s going to be a major problem when both devices’ data gets synced to the main file.

Does Every Table Need a Primary Key?

Every table doesn’t need a primary key–look ups, value list tables, etc. However, there’s not really a problem to have primary keys in every table (in fact, the default fields ensure this to be the case).

There’s a look up kind of table, one that holds information in it about a unique record. A Country Code table, for example, could use “US” as a primary key since that is unique to the table. As long as we’re not using “US” for relational logic, it’s okay. Look-ups are different.

Primary Key Implementations

FileMaker Primary keys are indexed. Text keys have two indexes, and that can increase the size of the file. But this is rarely a problem.

Default Fields tag a field as a primary key, and those are needed for Add-ons. We think Claris is moving towards supporting primary keys.

Naming FileMaker primary keys: this is up to the developer, but just be consistent. We use <<TableName>>_ID for the primary key.

The Data Migration Tool auto-increments the serial number.

Using Primary Keys with Integrations

Using primary keys with integrations is tricky in many cases. Our product LedgerLink handles the issues, and its method came from Todd’s extensive tinkering with the idea. Here’s a brief summary:

Integrations bring to the table a more complex problem. As far as primary keys are concerned, It is tempting to use the FileMaker primary key as the link between an invoice in Quickbooks Online and a FileMaker app, such as LedgerLink. After all, QBO has all the invoices and so does the FileMaker app. The problem comes in restoring invoices from QBO to your FileMaker app. All the primary keys you used for the links will be gone (the records are deleted) and as invoices are imported into the FileMaker app, new primary keys will be generated. The link breaks.

So the QBO keys (established in QBO and never changing) should be used as the link. Easy? Not so fast. . . In QBO, there is no such thing as invoice lines in the API we get back. Lines of an invoice are packaged up with the invoice, and there’s no foreign keys for each line.
In FileMaker we need primary keys and foreign keys to set up the proper UI and relationship. We can’t use the QBO Ids.

So we really need both. We need to build a system that maintains the link for each invoice to QBO through its IDs but also use Primary keys and foreign keys to build what we need in FileMaker.

Thinking about Primary Keys in Practice

Generally, we have to think, when using Integrations that become your source of truth and from which you might have to restore data into FileMaker, you have to consider this idea and work to maintain the links between the integration’s end point and your FileMaker apps. There’s a lot more; maybe we’ll explore that in a future episode or blog post.