AnsweredAssumed Answered

Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

Question asked by philmodjunk on Jun 16, 2010
Latest reply on Jun 28, 2010 by philmodjunk

Title

Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

Post

Every now and then, I encounter a database created by another that uses a calculation field or auto-entered data field with Get ( RecordID ) as the calculation expression, and it is then used to uniquely identify each record in the table and also to link to records in related tables.

At first glance, it looks like a very reasonable alternative to using an auto-entered serial number field as the Primary Key, but there's a subtle limitation to this approach that will result in a major headache. Sooner or later, you will need to import this data into a different filemaker database either to recover the data from a corrupted file or simply to deploy a newer version with updated table definitions. If you rely on Get ( RecordID ) as your primary key, this import likely will visit chaos on your data and relationships. This will happen because each imported record is automatically assigned a new record ID which may be completely different from its original value.

Consider this simple example with a table with 3 records:

Record ID      Data      
1                      Alpha
2                      Beta
4                      Upsilon

If I save a clone of this file and import this data into my clone, I will get:

Record ID      Data      
1                      Alpha
2                      Beta
                     Upsilon

And the third, Upsilon record will no longer link to the same related records as they all have 4 in their match fields.

Simply sorting the source file's found set in descending order by "Data" just before importing would produce these Record ID's:

Record ID      Data     
1                      Upsilon
2                      Beta
                     Alpha

Furthermore, there is no way to alter a record's Record ID in filemaker so you have no way to correct this error after the fact. You'd have to sort your original file by Record ID and carefully insert new dummy records to fill in each "gap" in the Record ID sequence, import the data and then delete the dummy records in the new file after the import is complete.

Thus in Filemaker databases, a simple auto-entered number field remains the simplest, safest way to implement a Primary Key.

Outcomes