 |
|||||||
|
 |
|
|||||
|
 |
|
|
||||
|
 |
|
|||||
|
Referential IntegrityOne 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. 0001 SUBROUTINE INTEGRITY.CHECK 0002 $INSERT BP, AREV.COMMON 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 * 0016 RELATED KEYS = "RECORD<FIELD(NEXT INDEX,"TM,4)> 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,"","","") END END WHILE CHECK_REC REPEAT RETURN" (Volume 2, Issue 2, Page 4) |
|
|||||
|
 |
|
|||||
|
| |
|||||
|
|||||||