![]() |
![]() |
|
![]() |
Do It Yourself Referential Integrity By: Liz Woodhouse ParadoxCommunity.com Introduction This article assumes the reader is familiar with interactive Paradox®, ObjectPAL® syntax and basic database design (papers on the later can be found on the Paradox Resources page at RDA World Wide). It also assumes the reader has read the Data Integrity Introduction and Referential Integrity Introduction articles on this site. The referential integrity feature built into Paradox has certain limitations (discussed in the Referential Integrity Introduction article) which can make managing tables linked by RI difficult, and which to not meet all RI needs. In this article, we'll focus on how to use ObjectPAL® code to do what the built-in referential integrity (RI) does as well as what it doesn't do. What's Included: the Built-in RI We can't replicate what the built-in RI does without knowing what that is, so first we'll review that briefly:
OR For 4-6, these things will all have to be controlled by the developer, and I'll let said developer decide how to do that. For 3, follow the procedures outlined below for deleting records, with the necessary modifications. Some general advice to make things easier Please note that this advice reflects my opinion and makes mention of things which deserve their own articles. They are mentioned here only briefly as things which I believe make ensuring data integrity, including referential integrity, easier.
Subject: TIP:PdoxWin:Not-in Queries:1999:09.26 Date: Sat, 10 Feb 2001 12:24:12 -0500 From: Michael Irwin [CTech] <mirwin@ctech.com> Organization: CTech Newsgroups: corel.wpoffice.paradox-faq How to implement referential integrity via code For all of the following steps, custom methods in a library should be used to reduce code maintenance and insure consistent handling. If you're not familiar with libraries, please check out Jeff Cranston's series on libraries. Please note that these steps discuss only a single child table. If there are multiple child tables, you should perform any changes / deletes / checks starting at the end of the chain of tables and moving back to the beginning. Thus, the second to last child table would function as the parent in the examples below and the last child table would function as the child. Once operations on the last child table were complete, the third to last child would be treated as the parent table and the second to last child would be treated as child table. This sequence would continue until all child table operations were complete, and then the top-level parent table operations would be performed. Also, it's important to remember that the child tables don't have to be in the data model to qualify for inclusion in the operations shown below. The following are just examples of how to handle these issues. There may be many ways to reach the same goal, so do what works best for your circumstance. Code should be modified according to your specific needs and tested thoroughly before put to use in a production environment. Preventing child values not found in the parent For key and foreign key fields, you should rely on the form's data model and links therein; in instances where, for some reason, you do not have the tables linked in the data model, you should include code to populate the child table's foreign key field with the parent record's key value. In both situations, you should not allow the user to edit the key or foreign key fields. For non-key fields, you can use table lookup to ensure that field values correspond with those found in another table. However, there may be times, for example, when dealing with someone else's design which you are not allowed to alter, when users have the rights to freely edit fields which are not controlled by any of the above constraints but which must reference a value in another table. In such cases, you can use the field's changeValue to check whether the incoming value is in the "parent" table or not. The following is an example of how to do this with a library method. It assumes the form has a library variable (libRI) which has already been opened. In the child field's changeValue event, place a call to the library routine which validates child values: ;// child field's changeValue code libRI.cmValidate(PARENT, "ParentField", eventInfo)The library method requires the following parameters:
method cmValidate(uioParent UIObject, stField String, var eventInfo ValueEvent) var tcLocate TCursor endVar ;// use a tcursor so the cursor on our form doesn't ;// move should a match be found tcLocate.attach(uioParent) ;// alternately, you could open the tcursor using ;// the table's name, or the tcursor could be ;// passed to this method ;// eventInfo.newValue() represents what the user ;// typed but which is not yet committed to the field if not tcLocate.locate(stField,eventInfo.newValue()) then msgStop("Stop", eventInfo.newValue() + " is not valid. Please enter a valid value.") ;// reset incoming value to field's former value eventInfo.setNewValue(self.value) endIf tcLocate.close() endMethodCascading changes in a parent field down to the child table(s) Linking fields should never change, and so cascading changes from a parent to a child should not be needed. I acknowledge that you may find yourself dealing, for example, with someone else's design and therefore have the need to cascade changes from a parent down to its child records, or you may be in a situation where a lookup value needs to be changed everywhere that it's been used. However, I have yet to find a completely reliable way of allowing this to happen while the system is in use, and yet code would only be needed if this happened regularly while a system was in use. So, rather than provide code which is not completely reliable, I will simply recommend doing whatever it takes to alter the design such that cascading changes are not required. If this is not an option, then I would recommend reviewing Stefan Bodingh's Transaction Basics article and implementing cascading changes only via a transaction so that you can rollback all changes should one fail. And whatever you do, make sure you trap every possible error and test extensively. Preventing parent changes or deletes when child records exist As mentioned in the previous section, changes to linking values should not be allowed. However, there may be situations (such as table lookup, or dealing with a design you aren't allowed to change) where a parent, or lookup, value which has not been used elsewhere can safely be changed. The change code in this section will deal with such situations. To prevent parent changes or deletes when a child table has matching values, use a custom method in the parent field's changeValue to search relevant child tables. As mentioned, this also works for lookup tables (you don't want to remove items from a lookup table if those items are in use), and similar situations. The following would go in the parent field's changeValue event to prevent the parent value from changing if that value exists in a child record: ;// if we're changing a non-blank value... if not self.isBlank() then ;// this if statement would repeat for every table ;// where this value might be in use ;// if the value is in use in a child table... if cmIsInUse(":ALIAS:TABLE.DB","Field",self.value) then ;// reset the field to its former value... eventInfo.setNewValue(self.value) ;// and inform the user msgStop("Stop", "This item has been used and cannot be changed.") endIf endIfThe following code for preventing deletion of a parent value when it has child records, is basically the same as the code above, but would be placed in the record or table-display object's action event: if eventInfo.id() = DataDeleteRecord then ;// if the user does not confirm deletion, ;// cancel via an errorCode if msgQuestion("Confirm Delete", "Permanently delete this item?") <> "Yes" then eventInfo.setErrorCode(UserError) message("Delete cancelled") return endIf ;// if the user confirms deletion, call the custom ;// method to find out if the value is in use, if it is ;// prevent the delete via an errorCode and inform user if cmIsInUse(":ALIAS:TABLE.DB","Field",self.value) then eventInfo.setErrorCode(UserError) msgStop("Stop", "This item has been used and cannot be deleted.") return endIf ;// if we get here, the delete is ok, ;// enter edit if necessary if not isEdit() then action(DataBeginEdit) endIf ;// delete will happen here if an error wasn't set endIfBoth of the above methods use the following custom method, which should probably be placed in a library where all of your forms can take advantage of it. This method requires the following arguments:
method cmIsInUse(stTable String, stField String, stValue String) Logical var tcUse TCursor endVar ;// open the table which could be using the value if not tcUse.open(stTable) then errorShow("Couldn't open " + stTable) ;// act like value's in use return True endIf ;// try to find the value in the specified field if tcUse.locate(stField, stValue) then tcUse.close() return True endIf tcUse.close() ;// if we get this far, value's not in use return False endMethodPreventing orphans when a parent is deleted To prevent orphans when deleting a parent, trap for the delete, check if there are children, remove the relevant children first, and only allow the delete if all relevant children can be deleted first. The following code does not address rollback issues (what if not all children can be deleted). Should the reader need rollback capability, I would recommend reviewing Stefan Bodingh's Transaction Basics article and combining the information there with that provided here. Also, the following example uses the tcursor empty() method which may or may not be suitable for your environment. Some alternatives are shown after this code block. var tcChild TCursor endVar ;// if the user does not confirm the delete, ;// cancel via an errorCode and return if eventInfo.id() = DataDeleteRecord then if msgQuestion("Confirm Delete", "Permanently delete this item?") <>"Yes" then eventInfo.setErrorCode(UserError) message("Delete cancelled") return endIf ;// repeat this section for each detail table involved ;// if detail tables have detail tables, perform the ;// empty for the last table in the chain first, ;// and work back to the first detail table - this will ;// require scan loops and setRange... ;// by attaching to the child table-display object, ;// we automatically limit the records impacted by ;// tcursor methods to the child records for the ;// current master record tcChild.attach(CHILD) ;// or open and set range to match parent ;// drop any user-set filters - only needed ;// if attaching to a uiobject tcChild.dropGenFilter() ;// if we can't empty the child table, cancel the ;// parent delete via an errorCode and inform user if not tcChild.empty() then eventInfo.setErrorCode(UserError) msgStop("Error","Couldn't delete detail records.") endIf tcChild.close() endIf ;// if we make it here without errors, ;// the master will deleteAlternatives to using tcChild.empty() include: for liCounter from 1 to tc.nRecords() if not tcChild.deleteRecord() then errorShow("Couldn't delete child record") quitLoop endIf endFor ;// or... while tcChild.nRecords() > 0 if not tcChild.deleteRecord() then errorShow("Couldn't delete child record") quitLoop endIf endWhile ;// or... while not tcChild.eot() if not tcChild.deleteRecord() then errorShow("Couldn't delete child record") quitLoop endIf endWhile Summary Referential Integrity is vital in any relational database. In this article, we have demonstrated that with a few simple routines, the developer can include all the necessary functionality of the built-in RI, add functionality not offered by the built-in RI and gain the flexibility that comes from doing it yourself. Discussion of this article |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 15 May 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|