How do you create composite primary keys in a table in Filemaker?
concat Get(UUID) & "|" to your multiple fields concat. This way you'll have a unique key but will be able to somehow decipher single info from the PK.
Thank you SIPLUS. So there is no "natural" mechanism in filemaker that allows one to define a composite primary key for a table? This is something that I am used to in MS Access where one is able to define foreign key relationships based on single or composite field keys enabling the enforcement of referentail integrity.
natural keys is going to downgrade your reputation on any database discussion forum. Of course you can create a composite primary key based upon fields, but don't brag around about having done it.
Access allows you to do that, and also has an easier referential integrity enforcement model if you want to disallow deleting of aparent records if child records exist. In FileMaker, you need to use their security model.
Regarding keys, though, why not just define an unique key (e.g. the UUID) as the primary key, especially if you were going to concat the UUID with other fields. Generally, it is not advisable to use actual data in primary keys.
Thank you again. We will agree to disagree on the merits of my approach however. Can you you tell me how this is achieved in filemaker? I can't see where I am able to select two fields (say) and make these the the unique ID. I can only see where this can be done to a single field.
it can be done with auto enter calculation where the calculation is concatenation of the values you want to combine as members of the key, and validation of the calculation field as unique + not empty.
Advisable? not really
Surrogate Keys or Natural Keys? | Data and Technology Today
to create a composite key, just concat.
Concat comes from last century's BASIC language, in Filemaker you use the & operator to do it, with some help of SerialIncrement or other voodoo to make sure your key length is constant.
Having a calc field that works on 2+ fields in order to create a key is a choice you have to ponder, though - why (over)load your table with calcs when you can solve the key problem with less impact on your cpu's ?
In other words, beware of what you want, you can get it but at a price.
You can't exactly "select two fields (say) and make these the the unique ID." However, you can define multiple predicates in the relationship, e.g. phone=phone AND email=email.
Adding to the responses already posted, the widely accepted rule for Key fields is that they should (1) exist purely for use by database schema, and (2) not be human readable or useable. It is advisable to follow those principles for Key fields.
What you are asking about ought to be considered as a separate issue—a human readable/meaningful code field. As already pointed out, this can easily be created using a concatenation calculation either as a calc field or as an auto-enter calc in a regular text field. Plenty of users like to have such a field, so by all means create one; just treat it separately from your Key fields.
Can you give an example of the key you want to see and why?
I agree that this is a bad idea, but if you really want to do it...
What you are proposing needs data entered on the record before the key is generated which is a little backwards since most keys are auto entered on record creation. Sounds like trouble in so many ways.
Just use a concatenation in your ID calc. The only thing that makes sense for auto-enter is if you want to serialize things from a parent table in a readable way.
ParentTable::ProductType_fk & Get(RecordID) !!!This is not a safe thing to use. EVER!!!!
This is also so wrong in so many ways.
Thank you everybody for the practical solutions and design advice. I have been looking at filemaker as a potential replacement for Access as I am moving to a Mac. I can see that what I want to do is not possible in filemaker without going through hoops that I philosophically disagree with. I shall look for something that behaves in a more natural and user friendly fashion.
The advice giving about FM is the best advice for any databases. I have program in several databases and the key field should not be seen or messed with by the user. Humans are prone to make mistakes. No hoops with FM, you just have to take the time to learn.
Re: "I can see that what I want to do is not possible in filemaker …" – that is patently not true; you have been advised by myself and others that you can create your field using a concatenation calculation—you do know what that is, do you?
Re: "I shall look for something that behaves in a more natural and user friendly fashion." – there is nothing "unnatural" or "unfriendly" about FM's functioning. By all means use Access if you think that, but from my understanding of things it will drive you bonkers more quickly than FM will.
Good luck, sir!
I don't think any database will serve you if you can't use logic or draw accurate and realistic conclusions.
Retrieving data ...