2 Replies Latest reply on Jun 18, 2012 10:27 AM by philmodjunk

    Primary Keys/Secondary Keys

    MichaelLawrence

      Title

      Primary Keys/Secondary Keys

      Post

      I'm somewhat new to FMP but I'm creating a somewhat simple customer/transaction database. I now know you can not simply 'set' a primary key in FMP but instead rely on unique auto-generated serial # by FMP. The issue I'm having is that my customer table is using a serial # as its primary key. My transactions table is also using a serial # as its primary key.

      I was told and have read that when viewing the database relationships tab, the field that is in italics is considered that table's primary key. If that is true, my customer table and my transactions table both view the CustomerIDNumber field (originally created as the 'primary key' in the customer table) as its primary key. I have set the relationship as a one to many, and am confused as to why the Transaction table isn't viewing the 'TransactionIDNumber' field as its primary field.

      My other question is I plan to create another table to be used in a subform and plan on using a combination of the TransactionIDNumber and Item number as a compound key. Is there any common sense that suggests not to do this?

        • 1. Re: Primary Keys/Secondary Keys
          davidanders

          http://help.filemaker.com/app/answers/detail/a_id/9922/~/understanding-and-creating-many-to-many-relationships-in-filemaker-pro

          What do I need to know to work with many-to-many relationships in FileMaker Pro?

          A student can take many classes and a class can have many students.  Related these two tables together would be difficult without the use of a join table.  The join table will contain the primary keys from the student table and the class table.  The join table can be called anything you want.  For this example we will call the join table “signups.”
          Image

          • 2. Re: Primary Keys/Secondary Keys
            philmodjunk

            Italics does not identify the primary key in Manage | database | relationships. It simply identifies that the field is a match field in the relationship. It could be either a primary or a foreign key.

            I now know you can not simply 'set' a primary key in FMP but instead rely on unique auto-generated serial # by FMP.

            With FileMaker 12, you now have the option of using a text field with an auto-entered Get (UUID) as the primary key. This approach can make moving data from one copy of the file into a new copy of the file much simpler to do as you don't have to update serial number settings with Get (UUID).

            My other question is I plan to create another table to be used in a subform and plan on using a combination of the TransactionIDNumber and Item number as a compound key. Is there any common sense that suggests not to do this?

            Why do you need to match values in two fields instead of just one? This is sometimes useful to do, but should only be done if there is a reason to do so.