In order for this site to work correctly, and for us to improve the site, we need to store a small file (called a cookie) on your computer.
By continuing to use this website, you agree to our cookies and privacy policy.
Home page Home page Home page Home page
Pixel Header R1 C1 Pixel
Pixel Header R2 C1 Pixel
Pixel Header R3 C1 Pixel

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.

0003       *
0004       * Get indexes description record and look for all "Related To" index
0005       * types,  (REVMEDIA Vol 2 Iss.  1)
0006       *
0007       CHECK_REC = XLATE("!":DATAFILE,"!":DATAFILE,1,"X")
0008       LOOP
0009          NEXT INDEX = CHECK REC[1,@SVM]
0010          CHECK_REC = CHECK_REC[COL2()+1,999]
0011          IF NEXT_INDEX[1,2] = "P$" THEN
0012             *
0013             * Field number in current records containing related data is in
0014             * text mark 4
0015             *
0017             IF RELATED KEYS THEN
0018                *
0019                * Construct warning message, split over several lines due to
0020                * width constraint of this format
0021                *
0022                MSG =DATAFILE:"Record ":@ID:" cannot be deleted"
0023                FORMAT = "C#" : LEN(MSG)
0024                MSG := @FM : "because it is related to" FORMAT
0025                *
0026                * Put name of related from file into FN
0027                *
0028                FN = NEXT_INDEX[3,@TM]
0029                FN = XLATE("DICT " : DATAFILE,FN,26,"X") [1,"*"]
0030                MSG:= @FM: (:the following ":FN:" records:") FORMAT
              MSG := @FM : RELATED KEYS FORMAT
              CALL MSG(MSG,"

(Volume 2, Issue 2, Page 4)
Pixel Footer R1 C1 Pixel