Referential Integrity

One of the prime considerations when evaluating relational databases is whether or not referential integrity is supported. At its simplest, this means "can records referenced by other records (EG Customer references related to invoices) be deleted whilst these references exist?". This is such an obvious requirement that the question may seem facile. The system should not allow the deletion of a customer if there are several invoices on hand for that customer. Unfortunately AREV seems to miss this point and permits the deletion of records with embedded foreign keys (although it will prevent the modification of these records by hand).

Using the knowledge gained in last issue's indexing article it is however possible to produce a piece of code which may be called from any pre-delete process to ensure that referential integrity is maintained. The steps involved are straightforward - at delete time, see if there are any "Related From" indexes on file, and if there are, see if the fields referenced thereby contain data. If they do, reject the delete request. The code below gives an example of how this information might be used to full advantage.

SUBROUTINE INTEGRITY.CHECK
   $INSERT BP, AREV.COMMON
   *
   * Get indexes description record and look for all "Related To" index
   * types,  (REVMEDIA Vol 2 Iss.  1)
   *
   CHECK_REC = XLATE("!":DATAFILE,"!":DATAFILE,1,"X")
   LOOP
      NEXT INDEX = CHECK REC[1,@SVM]
      CHECK_REC = CHECK_REC[COL2()+1,999]
      IF NEXT_INDEX[1,2] = "P$" THEN
         *
         * Field number in current records containing related data is in
         * text mark 4
         *
         RELATED KEYS = "RECORD<FIELD(NEXT INDEX,"TM,4)>
         IF RELATED KEYS THEN
            *
            * Construct warning message, split over several lines due to
            * width constraint of this format
            *
            MSG =DATAFILE:"Record ":@ID:" cannot be deleted"
            FORMAT = "C#" : LEN(MSG)
            MSG := @FM : "because it is related to" FORMAT
            *
            * Put name of related from file into FN
            *
            FN = NEXT_INDEX[3,@TM]
            FN = XLATE("DICT " : DATAFILE,FN,26,"X") [1,"*"]
            MSG:= @FM: (:the following ":FN:" records:") FORMAT
            MSG := @FM : RELATED KEYS FORMAT
            CALL MSG(MSG,"","","")
         END
      END
   WHILE CHECK_REC
   REPEAT
RETURN

(Volume 2, Issue 2, Page 4)