3 Replies Latest reply on Nov 24, 2013 8:44 AM by NormanSaxon

    Newbie - primary key for product database?



      Newbie - primary key for product database?


      I'm working on creating a Products database, where each product code (or sku) is unique and alphanumeric. For example, ABG624397-CXC would be one product number. 


      In this situation, what would be the best practice regarding the primary key? Is it okay to make the actual Product Code the primary key? Or is it better to create a separate field that is an auto-entered Serial number as the ID for the product?


      Thank you.



        • 1. Re: Newbie - primary key for product database?

          Hi Ron,


          The problem with using the Product Code as the primary key is that someone can make a mistake in typing it.  And then all relationships (child records) will inherit that code.  When it is corrected, you will have broken your child records.  Your product table, in fact ALL tables, should include first and most importantly, a unique auto-enter serial number.  It should have no meaning at all.  Let FileMaker generate the number (check field Options, auto-enter serial) and it would be called ProductID or CustomerID etc. 


          Many also preceed the name with pk (in the primary table, indicating it is the primary or parent key) and fk (in child tables, indicating it is a foreign key in the child table).  The keys are what will hold your structure together so best to let FileMaker do it for you. :smileyhappy:

          • 2. Re: Newbie - primary key for product database?

            Hi LaRetta.

            Thank you for the explanation and tip. It makes more sense now.


            Thank you!



            • 3. Re: Newbie - primary key for product database?


                   I am very new to FileMaker Pro 12 Advanced.

                   I am having a challenge with setting up primary keys.

                   I have two Excel spreadsheets that I have opened in FileMaker


                   The file come for a server and no data will be edited it is purely read only.

                   The commend field is the student number in both tables.

                   The student DB has one recorded per student

                   The ClassList has student course and student number only

                   I need to set up one to many each student cold have many courses

                   I guess what I’m asking is how do I set the student number as a primary key?

                   I need to be able to pick the class number and list all the students in that class.