Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


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:
  1. It prevents child values not in the corresponding parent key field(s).
  2. It does one of the following, depending on your selection:
    • cascades changes made to the parent key field(s) down to the corresponding field(s) in the child records

    • OR
    • prohibits changes to the parent key field(s) if there are matching values in the corresponding field(s) of any child records.
  3. It prevents emptying or deleting a parent table if a child table both exists and has values.
  4. It creates a secondary index in the child table on the fields involved (if one doesn't already exist).
  5. It can prevent Paradox for DOS from opening the tables (if the Strict Referential Integrity box is checked).
  6. It prevents restructures which would break the referential integrity relationship.
(Note that it does all these things even when accessing tables directly. I won't address trying to duplicate this at the table level, mainly because I don't believe it's possible to do this with complete control and also because I believe you should use forms to access tables, and developer access at the table level shouldn't need these controls.)

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.
  • Use meaningless, single-field LongInteger keys and single-field foreign keys. The importance here is to not allow key values to change. With a meaningless key, there is no need to change the value.
  • Normalize your data. It is much easier to maintain integrity within normalized tables.
  • The data model can help maintain data integrity by automatically populating a new child record's linking field(s) (foreign key) to match the current parent record.
  • Table lookup can help by ensuring that a field's value is a valid one, based on the values in another table. This works on new records; it will also work on existing records when said records are edited (validity checks will be re-checked on posting an edited record).
  • When first establishing RI where it hasn't existed before, use not-in queries (see the FAQ newsgroup and the thread listed below) to look for data which violates RI constraints, and resolve any anomalies before implementing your solution in a production environment.
  •      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
  • Always back up your data and test thoroughly.

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:
  • uioParent = the parent object which contains all valid values
    alternately, you could pass the alias & table name, or a tcursor
  • stField = the name of the field in parent table
  • eventInfo = eventInfo - so we can get the incoming value and reset it if needed
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()

endMethod
Cascading 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
endIf
The 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
endIf
Both 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:
  • stTable - the name of the child table (or the table which uses lookup)
  • stField - the name of the field in that table where the value might be in use
  • stValue - the value the user is trying to change or delete
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

endMethod
Preventing 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 delete
Alternatives 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.