Beginner's Question: Why is Primary Key a Text Field?

[tatihulot]

Hello.  I'm accustomed to working with MS Access, but have begun looking into FileMaker.  I've watched a few tutorial videos first, then began making a simple music collection database.

I noticed that when FM creates a table, it automatically adds five fields.  One of them is Primary Key.  It's designated as a Text field.  I've always been under the impression a Primary and Foreign Key field have to be numbers (preferably auto-generated), and that you should use numbers because a text string can be mistyped.

May I please ask why FM makes the Primary Key a text field and not an actual number?  Thank you.   

[Ocean West]

The default fields primary key field is auto enter UUID which needs to be a text since the value is a string, you can get weird results if you make it a number depending on the first character. 

There is another option for UUID Number potentially saves some 'space' might be marginally faster but unless you guarantee your solution will never become a source for oData or DataAPI, and you may also have to be careful to declare that as a string when adding it to a JSON object. 

You can also use sequential serial number as a key, but is old' school thinking, especially if your solution may grow and someday need to support syncing. Also if your doing live development you have to know if you are using serial numbers and explicit in checking for unique values you can lock schema and a user or a server scheduled script that might want to create a record in the table you are modifying may fail and just overwrite the record they happen to be on. 

Keys between tables should have no real meaning and ideally should not be user facing. They serve as schema to relate data. 

If you need a human readable number you may add a serial number or use this https://scalefm.com/sequence-generator/

 

 

[Fitch]

I've gone down the UID road and have gone back to using plain 'old school' serial numbers. I would only use UIDs when there's a scenario that warrants it, e.g. certain syncing situations. YAGNI

[Ocean West]

these days deailing with more distributed api sytems i find UUIDs become essential.

[bcooney]

Another benefit of uuids is that they are unique across tables within a solution. That allow a relation to another table, such as status, using an abstract foreign key such as entityid.