Referential Integrity

In this section:

The term referential integrity defines the type of consistency that should exist between parent and descendant segments.

FOCUS can provide referential integrity for the following types of data sources described in one multi-segment Master File:

The following sections discuss referential integrity constraints.


Top of page

x
FOCUS INCLUDE Referential Integrity

The FOCUS MODIFY facility syntax provides automatic referential integrity for inserting new records.

You must describe the data source in one multi-segment Master File. The multi-segment description establishes the relationship between the segments.

With a multi-segment Master File, you cannot add a descendant segment using the FOCUS MODIFY facility unless the parent segment already exists. Therefore, a MODIFY procedure that inserts records must MATCH on the parent segment before adding a record in descendant segment.



Example: Using FOCUS INCLUDE Referential Integrity

The following examples demonstrate referential integrity when adding new records. The scenarios are:

  1. Add a salary for an employee only if data for the employee ID already exists.
  2. The employee ID does not exist. Add both a new employee ID and a salary.

A simple, annotated FOCUS MODIFY procedure for each scenario follows.

The first example adds course information only if a record already exists for the employee:

   MODIFY FILE EMPWRITE
   CRTFORM LINE 2
   "ADD SALARY INFORMATION FOR EMPLOYEE  </1" 
1. "EMPLOYEE ID: <EMPLOYEE_ID  </1 "
   "SALARY: <SALARY          CURR_CODE: <CURR_CODE " 
2. MATCH EMPLOYEE_ID
   ON MATCH COMPUTE AQ0201_OCC = 0; 
3.    ON MATCH CONTINUE 
4.    ON NOMATCH REJECT 
5. MATCH AQ0201_OCC
      ON NOMATCH INCLUDE
      ON MATCH REJECT
   DATA
   END

The MODIFY procedure processes as follows:

  1. The user enters the employee ID, salary, and appropriate code. This constitutes the incoming transaction record.
  2. The MATCH command causes Adabas to search the data source for an existing record with the specified employee ID.
  3. If the employee record exists, the MODIFY sets the salary occurrence number to zero and continues to the next MATCH command.
  4. If no record in the EMPWRITE data source exists with the specified employee ID, MODIFY rejects this transaction and routes control to the top of the procedure.
  5. MATCH AQ0201_OCC causes Adabas to search for an existing salary record with the specified occurrence number for the employee ID located in Step 2. Because the occurrence number was set to zero in the previous MATCH command, no such record exists, and the MODIFY adds a new salary occurrence. If the occurrence number already existed, the MODIFY would reject the transaction as a duplicate.

The second example adds a record to the EMPWRITE data source for a new employee and adds a salary for that employee to the AQ0201 segment. If the employee ID already exists, the procedure adds only the salary information to the AQ0201 segment:

   MODIFY FILE EMPWRITE
   CRTFORM LINE 1 
1.      "ID: <EMPLOYEE_ID " 
2. MATCH EMPLOYEE_ID 
3. ON NOMATCH CRTFORM LINE 2
   "       LAST: <LAST_NAME        FIRST: <FIRST_NAME </1 "
   " MIDDLE: <MIDDLE_NAME> </1 "
   "  ADDRESS_LINE_CNT <ADDRESS_LINE_CNT> CITY:  <CITY "
   "  ZIP: <ZIP_CODE    COUNTRY: <COUNTRY  </1" 
   " DEPT: <DEPT   INCOME_CNT: <INCOME_CNT  </1"
   "LEAVE DUE: <LEAVE_DUE      DEPARTMENT: <DEPARTMENT "
   "SALARY: <SALARY  CODE: <CURR_CODE "
   ON NOMATCH INCLUDE 
   ON NOMATCH COMPUTE AQ0201_OCC = 0;
   ON MATCH COMPUTE AQ0201_OCC = 0; 
4. ON MATCH CRTFORM LINE 9
   "SALARY: <SALARY  CODE: <CURR_CODE "R_TAKEN  QTR: <QTR "  
5. MATCH AQ0201_OCC
   ON NOMATCH INCLUDE
   ON MATCH REJECT
   DATA
   END

The MODIFY procedure processes as follows:

  1. The user enters EMPLOYEE_ID.
  2. The MATCH command causes Adabas to search the EMPWRITE data source for an existing record for the specified employee ID.
  3. If the employee record does not exist, the user enters the data for both the employee and the salary. The procedure adds a record to each segment.
  4. If the employee already exists, the user enters only the salary data.
  5. The MATCH AQ0201_OCC command causes Adabas to search the AQ0201 segment for the specified occurrence. If this occurrence does not exist for this employee, the procedure adds it. If it does exist, the procedure rejects the transaction.

Top of page

x
FOCUS DELETE Referential Integrity

FOCUS provides automatic referential integrity for deleting records described in a multi-segment Master File. Just as with INCLUDE referential integrity, only data sources described in a multi-segment Master File invoke FOCUS DELETE referential integrity.

When you delete a parent segment in a MODIFY or Maintain procedure, FOCUS automatically deletes all descendant segments at the same time.



Example: Using FOCUS DELETE Referential Integrity

For example, when you delete an employee from the root segment in the EMPWRITE Master File, FOCUS also deletes all records from the descendent segments for the employee:

   MODIFY FILE EMPWRITE
   CRTFORM LINE 2
   "DELETE EMPLOYEE </1" 
1. "EMPLOYEE ID: <EMPLOYEE_ID   " 
2. MATCH EMPLOYEE_ID
      ON MATCH COMPUTE DOIT/A1 = 'N'; 
      ON MATCH CRTFORM LINE 6  
3.    "EMPLOYEE TO BE DELETED: <D.EMPLOYEE_ID </1"
      "         LAST NAME:   <D.LAST_NAME </1" 
      "         FIRST NAME:  <D.FIRST_NAME </1"
      "         DEPARTMENT:  <D.DEPARTMENT </1"
      "IS THIS THE EMPLOYEE YOU WISH TO DELETE? (Y,N): <DOIT "
      ON MATCH IF DOIT EQ 'N' THEN GOTO TOP; 
4.    ON MATCH DELETE
      ON NOMATCH REJECT
   DATA 
   END 

The MODIFY procedure processes as follows:

  1. The user enters the employee ID.
  2. The MATCH command causes Adabas to search the root segment of the EMPWRITE data source for an existing record with the specified employee ID.
  3. If the record exists, the MODIFY displays information for verification purposes.
  4. Once verified, FOCUS deletes the employee and all associated segment instances. When FOCUS deletes a parent instance, it automatically deletes all associated related instances.

Top of page

x
Inhibiting FOCUS Referential Integrity

You may not always want to enforce FOCUS referential integrity. If the referential integrity is being enforced between separate Adabas files described in a single Master File using an embedded join described in the Access File, you can describe each file you want to modify separately in a separate Master File

Another technique is to COMBINE data sources rather than using a multi-segment Master File. COMBINE of single data sources does not invoke FOCUS referential integrity.


Information Builders