K is for Key
In a relational database like FileMaker Pro, key fields are used to establish relationships between tables. Depending on where you get your information, there are many types of keys. In this article, I will focus on just two – primary and foreign keys.
Primary keys defined
A primary key (PK) is a field that contains data unique to the record in that table. As such, it can be thought of as a record identifier. There are some required characteristics of a primary key:
- never be empty
- contain unique data (in the table)
- never change
It is also most useful if it is not real or meaningful data. To understand this better, let’s consider some fields that seem to fit the requirements for a primary key but should not be used as such.
Avoid using these as primary keys
- tax file number (TFN)
- email address
- student number
- phone number
Although most people in Australia (and certainly those who are employees) will have a TFN, it can fail the PK test. First, if a person cannot provide their TFN for whatever reason, the field is left empty. Second, if the number is wrongly entered, it will need to be changed.
An email address could be shared by multiple people and may change.
A student number is generated by another database system. As for a TFN, it may not be available or may be incorrectly entered. Student numbers may be re-issued in some circumstances.
A phone number may be shared by multiple people and will likely change.
Good primary keys
The best primary keys are generated by the database system in which they are used.
The most basic is a simple serial number. In FileMaker Pro, the field option can be set to auto-enter a serial number on record creation. This will ensure sequential creation of unique serial numbers even in multi-user systems.
Many database systems can generate a universally unique identifier (UUID). In FileMaker databases (as in many others), the UUID is a unique 16-byte (128-bit) string. In the standard representation, the UUID is 32 hexadecimal (base 16) digits, displayed in five groups separated by hyphens, in the form 8-4-4-4-12. An example of this is:
012F5382-37FF-413B-A93C-D957A5C6F451
The function used to generate this is Get ( UUID ). To create a primary key in this way, set the field options to auto-enter a calculation that uses the function. We covered this (Set a primary key) in a previous blog article The A-Z of FileMaker: G is for Get.
Foreign keys defined
A foreign key (FK) is a field that contains a unique identifier from a PK of another table. In this way, the record with the FK is linked (or related) to the record with the key data in the PK.
Consider a FileMaker solution for recording issues with equipment. There is a table for equipment with a PK field. There is a table for issues with a PK field. The issues table also has an equipment FK field.
When a new issue is created, the unique identifier of the equipment is copied into the equipment FK field. This serves to relate the issue record to a specific equipment record.
By definition, the values in a FK will not be unique. In the above example, all issue records for a specific equipment item will have the same data in the FK.
Usually the FK will not be empty. In the above example, if the FK is left empty, the issue is not attached to any equipment and would be considered invalid. In some cases, it may be valid to leave the FK empty.
Generally the FK will not change. If it does change, this means that the record is relating to a different record. In the above example, the FK may be changed to correct an error when the wrong equipment was identified.
Referential integrity
Referential integrity refers to a rule that every foreign key value must match a primary key value in an associated table. In other words, the FK value must exist in one record of the other table.
Naming Keys – What do you think?
Most developers have opinions and conventions for naming their keys. For what it is worth, I name all my PK fields with a simple name – ID. I find that the context of the field is usually self-evident. It also allows me to copy and paste a PK into a new table.
I name my FK fields simply too – IDtable. In the above example, the FK would be named IDequip or IDequipment.
Here is an ERD from a previous post on the Join Table.
Do you have a naming convention for keys and a reason for using it? Feel free to leave a comment below.
Other keys
Often when we import data from other systems, we may be provided with a key from that system. It may seem like we have a ready-to-use PK supplied for us. Beware! Do not use the PK from another system. However, it is useful to store it with our data. In reporting, we can then refer to records with our key or their key.