6 Replies Latest reply on Apr 25, 2011 5:20 PM by MarlenMadsen

    Relational DB problem

    MarlenMadsen

      Title

      Relational DB problem

      Post

      I'm New to FM but have created many DB apps in the past. The question that I have is can I create in the Relational rules some where so that when I change the value of the Primary key of the parent table, have it cascade and change the linking field of the related records of the child table. Yes I understand that I could create a hidden serial field as the link. But I'm just asking the question as my previous developing tools relationships where always maintained with the rules that I specify

      I'm just guessing that FM does this but I don't see where other than creating my own script to do so?

      Amy thoughts would be appreciated.

      Thanks

        • 1. Re: Relational DB problem
          RickWhitelaw

          Why would you want to change the value of the PK in the Parent Table? Apart from that question, I believe that since changing the value of the PK in the Parent Table will break the relationship to related records, the answer to your question is "no".

          RW

          • 2. Re: Relational DB problem
            LaRetta_1

            "I'm New to FM but have created many DB apps in the past. The question that I have is can I create in the Relational rules some where so that when I change the value of the Primary key of the parent table, have it cascade and change the linking field of the related records of the child table."

            As Rick indicates, you wouldn't want to change the primary key in the parent table but rather, you would change the foreign key (this primary parent key which resides in a related tables) instead. 

            For instances, if you have a Product table with pk_ProductID (auto-enter FM-generated serial) and you have invoice LineItems with fk_ProductID, you change the fk_ProductID to a different product or rather, delete and re-add a different product.  When you repoint the related data, the information will flow accordingly.

            But I wonder if you could describe the specific tables involved and why you want to change a key.  Normally keys should not be changed once selected (exception would be value lists which are tables such as a Types table and you want to change the Type specified on a record.)

            • 3. Re: Relational DB problem
              MarlenMadsen

              Yes I understand Your ideas and I guess I will have to revert to let FM auto Generate the serial Parent key. My instance is that the parent key is a customer account number it is unique and the child records are payment history one to many. I only bring this case up as other Database developer programs will automatically change all related child keys if the parent key gets changed. In my case I made an error in the account num very rare but it happens so I correct it. FM will delete all child records if the parent record is deleted but won't cascade parent key changes to child records. 

              I only bring this up because other DB developer programs do this and was wondering if FM did and I am not seeing it

              Thanks for your reponses

              • 4. Re: Relational DB problem
                LaRetta_1

                "In my case I made an error in the account num very rare but it happens so I correct it"

                And that is why the keys which hold your relationships together should be meaningless (auto-enter, FM-generated serials).  And this holds true in Access, Approach, dBASEIV, Paradox etc.  Cascading key change may happen, it can be implemented in SQL as well, but it is very dangerous.  When you are relating to other FM files, ESS, ODBC and other sources, you cannot guarantee that those sources will be open.  Many will NOT cascade change.  We aren't even talking about imports from Excel which would break etc.

                If you use meaningless primary keys in ALL your tables, you never run into these issues; typos happen and once children have been related, you risk breaking relationships.  But no, FM cannot cascade change keys, sorry. 

                • 5. Re: Relational DB problem
                  philmodjunk

                  Having used a product (MS Access) that has a Cascading Update feature for protecting referential integrity, I liked the feature at first, but then discovered that such a cascading update could easily slow database performance to a crawl while thousands of related records updated. Since I could avoid the need for such updates simply by avoiding the use of primary keys that contained "meaning", it quickly became an unused feature in my systems.

                  • 6. Re: Relational DB problem
                    MarlenMadsen

                    Thanks for all your responses. I will try to break away from bad habits and use meaningless keys.

                    Thanks for your help.