6 Replies Latest reply on Nov 13, 2013 7:44 PM by MANOJKESWANI

    Can the primary key of a table be multiple fields?

    wladdy

      Title

      Can the primary key of a table be multiple fields?

      Post

      I am migrating from Access to Filemaker and I have the following question.

      Can the primary key of a table be multiple fields?

      In Access, I used to have a database with three tables: 'Days', 'Shows' to record performances given on various days and 'Acts' to record the various acts making up each performance.

      PK for 'Days' was field 'DayID'

      PK for 'Shows' were fields 'ShowDayID' (child of 'DayID') and 'ShowRank' (1, 2, 3, etc)

      PK for 'Acts' were fields 'ActDayID' (child of 'ShowDayID'), 'ActShowRank' (child of 'ShowRank') and 'ActRank' (1, 2, 3, etc).

      This system had the advantage of keeping all fields meaningful, avoiding artificial ID fields such as 'ShowID' or 'RankID'. Of course, the database was merely to record what had happened and thus the contents of the fields would never change once recorded.

      Is there a way to replicate a similar approach in FileMaker? Is there a better way altogether?

      Thank you very much!

        • 1. Re: Can the primary key of a table be multiple fields?
          philmodjunk

          It's certainly possible to do this in FileMaker, but I wouldn't recomend doing so in FileMaker nor would I set up a primary key this way in Access either.

          Using meaningful primary keys in any database system are not a good idea. You risk complications in the future if you ever need to change these keys to better reflect the "meaning" that they have. You can use such labels for searching and sorting on your database--that's often one of the reasons for adding "meaning" to such a key in the first place. It's just not the ideal choice for relating your tables.

          • 2. Re: Can the primary key of a table be multiple fields?
            LaRetta_1

            Nor would it be ideal in an Access database or any database.  Every record in every table should be identied by a unique serial key and that key should be used in relationships.  Seriously.  And yes, any field can be part of a key - you would create a concatenated calculation grouping the pieces but I will not tell you how because I don't believe in assisted suicide. :^)

            • 3. Re: Can the primary key of a table be multiple fields?
              wladdy

              Phil and LaRetta, thank you very much for your sound advice.


              My system has worked perfectly well on Access for many years, even after a few thousand records. It is probably because my data never changes once recorded. However, now that I am migrating to FileMaker, I will try to pick up good habits from the start!

              But if I don't use meaningful keys, the issue of populating the foreign keys of children tables becomes even more important and I still have not figured it out. I posted a detailed question in a new thread and your views would be invaluable! Thanks again.

              http://forums.filemaker.com/posts/176917383f

              • 4. Re: Can the primary key of a table be multiple fields?
                MANOJKESWANI

                     i really want to setup 2 fields to be the primary key. please help.

                • 5. Re: Can the primary key of a table be multiple fields?
                  philmodjunk

                       Threads this old no longer appear in Recent Items when a new comment is posted. I only found your post because I was searching for another thread. I suggest starting a new thread and asking your question there. You can include a link to this thread if you think that it helps you better explain your question.

                       But please explain WHY you want a two field primary key so that we can either: a) show you a better way to do it that does not require a two part primary key or b) so that we can clarify why what you want is OK, but isn't really a primary key in the first place. wink

                  • 6. Re: Can the primary key of a table be multiple fields?
                    MANOJKESWANI

                         two tables, customer and orders.

                         1 customer, many orders.

                         each order no is unique in sequence, but sometimes the nos are reused because of large time gap.

                         so i want a combined pri key.

                         thanks

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                        Contact                                            
                                        C_ID                     num                     pri key
                                        PFX                     tex                      
                                        FNAMES                     tex                      
                                        LNAMES                     tex                      
                                        REFR                     tex                      
                                        C_DATE                     date                      
                                        BDAY                     date                      
                                        ANIV                     date                      
                                                                                     
                                        contact6_orders                      
                                        O_ID                     num                     pri key
                                        status                     tex                      
                                        source                     tex                      
                                        orderDate                     tex                      
                                        order                     num                     pri key