7 Replies Latest reply on Jul 13, 2011 11:25 AM by aammondd

    Set Primary Key



      Set Primary Key


      I am new to FileMaker Pro.  I have used Access for years and have always set my own primary key for setting up one-to-many relationships.  It seems that FileMaker Pro does not allow a user to set a primary key.  For example, it makes sense to me to allow employee number to be the primary key for my employee table.  Is this possible in FileMaker Pro?  Thank you.

        • 1. Re: Set Primary Key

          You don't have a button that looks like a key that you can click to make that field the primary key, but you can define a field as an auto-entered serial number field and prohibit user modification of this value. The functional result in FileMaker is that you have made this field the primary key for your table and you can use it as such in relationships.

          • 2. Re: Set Primary Key

            First of all, thank you very much for your response.  But I must ask a follow up please:

            If I have been uniquely identifying employees and products with my own unique number, are you saying I cannot do that in FileMaker Pro?  How would I construct queries? (Assuming that I can perform Select (and other SQL-type) queries in FM: I have not gotten that far).  I would prefer to use my company's numbering system and not have to use an application's auto generated number.  It is VERY possible that I am missing something here in my basic understanding of database relationships (referential integrity)?  It makes more sense to me to set my own primary key.  Thank you for your patience and your answer!

            • 3. Re: Set Primary Key

              "It makes more sense to me to set my own primary key. "

              No, it does not.  If you create your own key then you can make a mistake.  Once you assign a primary key (which binds all your relationships together) and you have created children (which will inherit this parent key), you will break your relationships if you correct the primary key.

              Every table should have a primary key.  And every primary key for a table should be an auto-enter, FM-generated, MEANINGLESS, serial number which would be used for all relationships.

              For queries, search for the actual information.  It is just as easy.  If you create your own primary key, you also risk generating duplicates if the solution is used in multi-user situation (which most are).  If you wish, you can create a 'shortcut key' which would be made up of whatever you wish for quick searching (create calculation result of text which concatenates the data together) such as:

              LastName & "|" & Phone

              If you use meaningful data for primary key, example, last name and their name changes (woman gets married), it will break your relationships.  If you use year of first sale and the secretary types incorrect year, you will break your relationships.  All meaningful data might change or be incorrect and need to be changed.  Leave Primary keys in every table and leave them meaningless.  :^)

              • 4. Re: Set Primary Key

                Wow LaRetta!! I greatly appreciate your answer. I guess I need to force myself through a paradigm shift.

                Thank you very much for taking the time to give me such a thorough answer. 

                • 5. Re: Set Primary Key

                  As someone who also has worked with fairly extensive MS Access databases, LaRetta's advice to avoid keys based on meaningful data is also good advice for that database system. While you have a cascade update option that can prevent changes to a primary key from disconnecting the record from related child records, this tool can easily trigger massive cascading updates that bring your database to a screaching halt until the updates are completed--something that can be avoided when you make proper use of meaningless primary keys instead.

                  • 6. Re: Set Primary Key

                    HAHAHA meaningless primary keys LOL you guys (and gals) crack me up

                    PS - great explaination LaRetta

                    • 7. Re: Set Primary Key

                      Filemaker allows you to create relationships between tables on whatever fields you wish to create relationships for much as you build joins for queries. FMP uses these in a static way. In addition FMP uses a more human friendly Find option to select records from a user interface rather  than design specific queries with either various inputs or where criteria

                      Calculation fields are a huge difference in FMP and can perform a number of functions and in many circumstances can even be "queried"

                      Lastly the Scripting Language in FMP along with new FMP 10 and 11 script  triggers allow for a number of things often handled by VB code in Access.

                      I tend to look at FMP as more of an application building toolset rather than simply a database.

                      Just like the long ago Apple slogan "Think Different"

                      Sometimes what you did in access to produce a result is completely different methodology to produce the same thing.