1 2 Previous Next 27 Replies Latest reply on Mar 22, 2013 9:44 PM by Lemmtech

    Help me better understand: Primary Keys

    jbrown

      Hey all,

      I posted recently about using employee IDs. A lot of people gave me great advice. But now my mind is wondering: Does every table need a primary key?

      I understand that primary keys are needed for relationships. They have to be unique, and as I understand it are related to the foreign key, the same as the foreign key in the related table. Thus Table A has relationships in Table B through the primary key to the foreign key.

       

      But what about that related table? If that table is an ending table, that is, it is simply a list of student grades or student behavior records, why does table B need to have a primary key? It already has the kids' IDs in there to relate back to the Table A. Table A is the One, and Table B is the many. and that's where it ends.

       

      Forgive the ignorant question, but i realized in my tables, I don't include a primary key when I feel that table doesn't need to have a one-to-many relationship in it.

       

      I assume i'm doing wrong and will fix that, but i ask again, what's the point of a primary key in a end-table, so to speak? Do you worry about Primary keys in utility tables?

      thanks

      jb

        • 1. Re: Help me better understand: Primary Keys
          MattLeach

          Personally i've made it a habit to always have a primary key in every table that has data. You never know what changes you might make in the future that may require a relationship to that table so its always better to have something already established IMO.

           

          The only table i do not put a primary key in is if i have a utility table or a table for just globals.

          • 2. Re: Help me better understand: Primary Keys
            coherentkris

            http://en.wikipedia.org/wiki/Unique_key

             

            I am in agreement with ML.. not every table needs a key field.

            • 3. Re: Help me better understand: Primary Keys

              I agree with Matt that every table should have a primary key and it has nothing to do with relationships that bring this strong conviction.  Take your example of an 'ending table' such as Student Grades.  Assume a power User with rights accidently deleted 150 student grade records.  What do you do?  What you do is grab backup and import by matching on keys. 

               

              There are many such examples where data retrieval and maintenance requires the record's unique identifier and attempting to add the key after the fact is too late.  Keys are cheap and all data tables should have them.  No keys, no salvation. 

              1 of 1 people found this helpful
              • 4. Re: Help me better understand: Primary Keys
                MattLeach

                This example is reason enough to have a primary key in every table. An ounce of prevention......

                • 5. Re: Help me better understand: Primary Keys
                  Lemmtech

                  I agree every data table should have  a primary key whether you are using it for a relationship or not.

                  • 6. Re: Help me better understand: Primary Keys
                    jbrown

                    LaRetta,

                    That completely makes sense. I hadn't thought of that.

                    Okay. I'll go through my tables and create primary keys for each one.

                    Thanks

                    • 7. Re: Help me better understand: Primary Keys
                      Stephen Huston

                      And remember that creating a new Primary Key field is the first of 2 steps:

                      1. create the primary Key field
                      2. populate all empty primary Key fields in older records that are already in the system.

                      I found files in inherited systems which had a pKey late in the field creation order, but lacked IDs in some records because step 2 was forgotten when the pKey field was added.

                      1 of 1 people found this helpful
                      • 8. Re: Help me better understand: Primary Keys

                        Also remember that every table should have an auto-enter creation and modification date (or I prefer timestamp).  When something happens and you need to look through the back-end data, these fields are critical in determining an issue.  Between these timestamps, the Account Name listed and your Server logs, you can pretty-much back-track to a specific issue AND be able to correct it because the timestamps help you isolate only those records that were affected.

                        • 9. Re: Help me better understand: Primary Keys
                          jbrown

                          I've got that in all the tables. The Createdby and CreatedTime auto entries, as well as the modification dates and people have saved me on more than one occasion. I can look back and see who messed things up.

                          • 10. Re: Help me better understand: Primary Keys
                            Lemmtech

                            Stephen makes a very good point and can often be overlooked. Making sure ALL record get populated with keys even old ones. Good of you to remind people of this.

                            • 11. Re: Help me better understand: Primary Keys

                              A trick I discovered to quickly populate a table if you are using regular serials (which I prefer):

                               

                              1. Make note of your total record count in the table. All records do not need to be showing
                              2. Go to field definition and create your ID
                              3. Set it as a type CALCULATION first =  Get ( RecordNumber )
                              4. Do NOT make it unstored -
                              5. Exit out of definitions
                              6. Go back in and change it to auto-enter serial (ADDED: and be sure to increment to the next number)

                               

                              It ignores the sort order of existing records and it is faster than anything else I have timed, surely because it is not iterating through records.  And back up first and do NOT do this while served.  But I change grouped data this way when normalizing data during migrations in many ways using different calculations.  Stored calculations plant the data as regular data.

                              • 12. Re: Help me better understand: Primary Keys
                                jbrown

                                LaRetta,

                                Unfortunately I can't take it off the server right now. My system won't work as a local copy. It gives me the dreaded "The Access Privilages have been tampered . . . " And it won't work. I've contacted a few outside companies but right now it's not working offline. It only works online.

                                 

                                I'll probably end up using a server-side script to give each record a Primary Key.

                                 

                                I'm sending the file to FileMaker soon to see if they can work on it and fix it. Otherwise I get to rebuild it.

                                 

                                jb

                                • 13. Re: Help me better understand: Primary Keys
                                  DavidJondreau

                                  As your attorney, I advise you to use UUIDs instead of serial numbers.

                                   

                                  LaRetta, this is an excellent way of populating fields, but why do you reccomend not doing this while the file is served?

                                  • 14. Re: Help me better understand: Primary Keys

                                    The reasons that *I* do not work in schema while served (other than to quickly open and review graph or fields):

                                     

                                    • Tests have shown that you can stop new-record creation (if in field definition) and script would fire on existing record, incorrectly changing data.  Some call it negligible risk but client data is not up for risk in my book.   What would happen if you change a calculation which affected privileges in the middle of a script which then must change its perspective (thus path) partway through?
                                    • When changing field definition or relationship, and if connection is dropped,  that is when it will  toast your file more likely than any other time.  I will only run data-corrections locally for much the same reason.
                                    • If a User is locking a record and you change schema which affects the data, will it hang you in calculation until record free? Or will it hold up Users?  They are hung up or you are hung up ... both bad.
                                    • Schema changes are, many-times, experienced by the User as blips, shakes, blank screens and hesitations.  Put 20 Users in a room with you and go to town on served files and watch their expressions (you will be shocked at how much they will notice).  Each time we unsettle the User, we shake User faith in the system and that is the biggest penalty of all.
                                    • Can we really predict all possible configurations and their outcome, with every calculation and its dependencies and every script being ran in the instant, with various permissions and record-locks?  Because it is unpredictable, it is unsafe.

                                     

                                    With just the IDs, the risk isn't as great but in principle you are still setting records and you can't test if all of them set correctly or were locked by User at the time.  In the time you finish and then check the records to find any which didn't create a serial,  a User could have created a child off that record without an ID if 'Allow Creation' as on and they enter data in a child field.  Break.

                                     

                                    Hey, David, I think I have come up with a new curse ... my prior favorite curse was Chinese which is "May you live in interesting times."  But I think I'll change it to  "May you change your schema across WAN!!"

                                     

                                    I admit to being fanatical about protecting data but really, we are all HERE because of the importance of client data and to put that data secondary to developer convenience is just wrong in my book.  I consider you to be a better developer so I am curious why you asked.  If you wish to scold me for being too tight then it won't be the first time and I don't mind and convincing arguments might even loosen me up a bit (although I doubt it).

                                    1 2 Previous Next