Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Cascading deletions to child tables

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Cascading deletions to child tables

    I can see there have been several posts on this subject over the years, but I couldn't find one that solved my problem.

    I have changed the 'Referential Integrity' of my child records in the relevant set. However, when I delete a parent record in that set, the child records are not being deleted.

    Unfortunately I have a few thousand records to delete so I was concerned at leaving many thousands of orphaned records. Is there a way to identify orphaned records? Maybe I could delete them manually after deleting the parents.

    Any advice would be very welcome

    #2
    What database are you using?

    Comment


      #3
      Hi, I'm using the Alpha built in .dbf

      Comment


        #4
        I use SQL Server which can be setup to handle cascading deletes. I think you have to handle that with Alpha Code when using dbf's. If you can, I would try using SQL Express. Free and a more robust database.

        Comment


          #5
          Thank you very much for the advice. I did consider moving to SQL a few years ago, but I decided that as everything was working OK, I was 'afraid' to take the leap into the unknown.

          Having said that, I am surprised that something as regular auto-deleting child tables is not just a few clicks away in Alpha - surely everyone needs to do it. I am unsure of the ramifications of hundreds/thousands of orphaned records in a dbf

          Comment


            #6
            Hi Larry.
            Look at the Pack function.
            That will delete records marked for deletion, as in potential orphans.
            At your own risk, of course.

            Try it on a test scenario with a good backup first.
            See our Hybrid Option here;
            https://hybridapps.example-software.com/


            Apologies to anyone I haven't managed to upset yet.
            You are held in a queue and I will get to you soon.

            Comment


              #7
              Originally posted by Ted Giles View Post
              Hi Larry.
              Look at the Pack function.
              That will delete records marked for deletion, as in potential orphans.
              At your own risk, of course.

              Try it on a test scenario with a good backup first.
              The issue is which child records should be deleted.

              Packing is only by table.

              I usually have a deleted field on the parent to keep the deleted records and use the child records in a set linked to the parent so I know that child record had it's parent deleted.
              Al Buchholz
              Bookwood Systems, LTD
              Weekly QReportBuilder Webinars Thursday 1 pm CST

              Occam's Razor - KISS
              Normalize till it hurts - De-normalize till it works.
              Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
              When we triage a problem it is much easier to read sample systems than to read a mind.
              "Make it as simple as possible, but not simpler."
              Albert Einstein

              http://www.iadn.com/images/media/iadn_member.png

              Comment


                #8
                According to the documentation, deleting a parent will mark the children - now orphans - for deletion.

                Searching for records marked for deletion in the child tables will show those for consideration. There may be several tables in the set.

                Packing the child table should remove them. Given "a few thousand records" -presumably Parent records, holding onto them for security reasons is a different issue.
                See our Hybrid Option here;
                https://hybridapps.example-software.com/


                Apologies to anyone I haven't managed to upset yet.
                You are held in a queue and I will get to you soon.

                Comment


                  #9
                  Originally posted by Ted Giles View Post
                  According to the documentation, deleting a parent will mark the children - now orphans - for deletion.

                  Searching for records marked for deletion in the child tables will show those for consideration. There may be several tables in the set.

                  Packing the child table should remove them. Given "a few thousand records" -presumably Parent records, holding onto them for security reasons is a different issue.
                  That is when referential integrity is on.

                  Larry noted that he changed the referential integrity setting. I'm guessing that means he turned it off (or on)??...

                  Larry please clarify.

                  Consider having a set where the parent child relationship is reversed.
                  Then you know which 'child records from your first set' are orphans and can be deleted.
                  Al Buchholz
                  Bookwood Systems, LTD
                  Weekly QReportBuilder Webinars Thursday 1 pm CST

                  Occam's Razor - KISS
                  Normalize till it hurts - De-normalize till it works.
                  Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                  When we triage a problem it is much easier to read sample systems than to read a mind.
                  "Make it as simple as possible, but not simpler."
                  Albert Einstein

                  http://www.iadn.com/images/media/iadn_member.png

                  Comment


                    #10
                    Larry, going forward I believe the recommended technique is to delete records from the "lowest" level of the set structure first. Then work your way "up" the set structure deleting the desired records from each "higher" table until finally you reach the parent table. Then you can safely delete the one record in parent without leaving orphans.

                    Comment


                      #11
                      Larry,
                      Given what Tom wrote, what is the set structure? How many levels - parent-child-grandchild? How many total child records in table?
                      Mike W
                      __________________________
                      "I rebel in at least small things to express to the world that I have not completely surrendered"

                      Comment


                        #12
                        Larry,
                        I took a little time to make an example application that has functionality to identify orphan child and grandchild records and removes them after the parent records have been deleted and the orphans created. See attached. There is a help file on the menu to explain a couple things. See if it is of any use at all for you.
                        Attached Files
                        Mike W
                        __________________________
                        "I rebel in at least small things to express to the world that I have not completely surrendered"

                        Comment


                          #13
                          Originally posted by Mike Wilson View Post
                          Larry,
                          I took a little time to make an example application that has functionality to identify orphan child and grandchild records and removes them after the parent records have been deleted and the orphans created. See attached. There is a help file on the menu to explain a couple things. See if it is of any use at all for you.
                          Mike

                          Very nice.

                          I made a sample with 700 parent records to test the speed.

                          It took awhile to generate that many test records, so I'm sending you the large data tables

                          It is slow especially with a volume of data and is helped by using an inverted set to id the child orphans quickly and delete them.

                          Unzip the attached file with a inverted child to parent set into another folder.

                          You can delete parents - I removed the orphans_display() in form fs_pcg2 on activate and left it on the button -
                          then run the operation named delS_OrphanedChildren and see the speed difference.

                          This concept can be expanded to include grandchildren in the set and a separate operation to delete the grandchildren first.

                          This does show the power of an inverted set in identifying orphans ( and other things...)

                          orphans.zip
                          Al Buchholz
                          Bookwood Systems, LTD
                          Weekly QReportBuilder Webinars Thursday 1 pm CST

                          Occam's Razor - KISS
                          Normalize till it hurts - De-normalize till it works.
                          Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                          When we triage a problem it is much easier to read sample systems than to read a mind.
                          "Make it as simple as possible, but not simpler."
                          Albert Einstein

                          http://www.iadn.com/images/media/iadn_member.png

                          Comment


                            #14
                            Hi Al,
                            Time is relative :) The 700 record build for me is 36 seconds and isn't really a part of the actual database activities being records are build one at a time as the database is used. The parent delete was 6 seconds. The orphan delete 32 seconds. But an approach that takes less time .... better. I'll check out the inverted set.
                            Last edited by Mike Wilson; 09-14-2021, 09:22 AM.
                            Mike W
                            __________________________
                            "I rebel in at least small things to express to the world that I have not completely surrendered"

                            Comment


                              #15
                              Originally posted by Mike Wilson View Post
                              Hi Al,
                              Time is relative :) The 700 record build for me is 36 seconds and isn't really a part of the actual database activities being records are build one at a time as the database is used. The parent delete was 6 seconds. The orphan delete 32 seconds. But an approach that takes less time .... better. I'll check out the inverted set.
                              Yes the build of the test data isn't the issue, but the deletes are.

                              What timing differences did you find when comparing the orphan deletes?
                              Al Buchholz
                              Bookwood Systems, LTD
                              Weekly QReportBuilder Webinars Thursday 1 pm CST

                              Occam's Razor - KISS
                              Normalize till it hurts - De-normalize till it works.
                              Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                              When we triage a problem it is much easier to read sample systems than to read a mind.
                              "Make it as simple as possible, but not simpler."
                              Albert Einstein

                              http://www.iadn.com/images/media/iadn_member.png

                              Comment

                              Working...
                              X