What is FM's Primary Key Equivalent?

[jdanniel]

Hi everyone.

Just a quick question: I don't see an option for a primary key in FileMaker 9.

I'm assuming FM uses slightly different nomenclature for it.

So, what is FM's equivalent? Where do I find it when creating tables and fields?

Thank you! Jd

[bcooney]

There is no primary key field type. Typically, a number field serves as a primary key. Its entry options are auto serial and do not allow modification is set to on. Depending on the naming conventions that you follow, you could name it __kP_PrimaryKey or EntityID. The only rule, is that each record of the table has a unique value. They should be meaningless.

[jdanniel]

No primary key...I'm going to have to get used to that.

Does this mean I can join two identical fields in different tables, for a one-to-many relationship, as long as they are numerical?

[Fenton]

Text or number. Either works, though there are slight differences between them.

[gdurniak]

actually, FileMaker does generate a primary key for it's own use, but it is hidden, and not intended for general use

the Get(RecordID) function "Returns the unique ID number of the current record. This number is a decimal value (an integer) generated by FileMaker Pro when the record is created. It does not change"

it can come in handy, when you need to compare records to an older backup, and there is no unique id of your own

greg

[SurferNate]

I think most of us that have been working in FM for a little while generate our _kP with an auto-enter text field. Mine is a concatenation of random and non random values, that makes for a totally unique primary key and also at a glance tells me a a little about the record, such as creation date and order. I wrote it such that i can use it everywhere, and it always results in a totally unique key. I think I learned this several years ago from OceanWest...

And yes, you can typically use almost any field that contains alpha-numerical data as a match field. This is a great way to do lookups and create a relationship based upon something the user knows and sees, but still have your primary relationship based on something the user doesn't know or see.

[bcooney]

I beg to differ. I believe that the majority of FM developers use a numeric auto-enter serial number for their primary keys (__kP_KeyField_ID). I strongly suggest that the number in this field is meaningless. It is not a calc of other numbers.

Having worked with SyncDek, I do appreciate creating a UID, as they do with their SyncID. However, I don't use that as my primary key. If you need to know created date, have a zz_CreatedDT field for that data. As for order (of creation?), an autoenter serial does infer order of creation. But avoid the "I don't want to skip a number" requirement.

[SurferNate]

I would not argue that the auto-enter serial is perfectly stable in most pre-planned and properly structured environments. I use a complex primary key fundamentally because several years ago the argument was made to me, somewhat convincingly, that a complex unique key is more explicit, and carries better across solutions, files, and tables. Especially when you want to change things over time...

Actually I have two very good reasons to use the complex key method. I have discovered on more than one occasion that I wanted to change from mutiple source tables to one multipurpose source table. Having a totally unique primary key makes this kind of structural switch easy. Also, If I had simple integer numerical primary keys then merging and importing groups of records, either across tables or a just simple import-and-add from a separate copy of the database, especially records with pre-existing explicit relationships, can become exponentially more difficult.

Now, as to storing info, especially user accessible info in the key, of course not. It just happens that I use a complex key that, if I want to look (which I occasionally do in the development process), tells me something useful about the record at time of creation.