x

FOCUS MODIFY facility syntax provides automatic referential integrity for inserting new rows in a related set of tables. The following rules apply:

• You must describe the related set of tables in one multi-table Master and Access File. The multi-table description establishes the relationship (an embedded join) based on the primary and foreign keys in the tables.
• The primary key rows belong to the parent table in the description. The foreign key rows belong to the related table in the description.

  With a multi-table Master File, you cannot add a related row (foreign key) using the FOCUS MODIFY facility unless the primary key value already exists. Therefore, a MODIFY procedure that inserts rows must MATCH on the parent table before adding a row in a related table.

Top of page

Example: Using FOCUS INCLUDE Referential Integrity

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

1. Add a course 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 course.

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

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

    MODIFY FILE ECOURSE
    CRTFORM LINE 2
    "ADD COURSE INFORMATION FOR EMPLOYEE  </1" 
1.  "EMPLOYEE ID: <EMP_ID  </1 "
    "COURSE NAME: <CNAME  "
    "GRADE: <GRADE "
    " YEAR TAKEN: <YR_TAKEN     QUARTER: <QTR   " 
2.  MATCH EMP_ID 
3.    ON MATCH CONTINUE 
4.    ON NOMATCH REJECT 
5.  MATCH CNAME
      ON NOMATCH INCLUDE
      ON MATCH REJECT
    DATA
    END

The MODIFY procedure processes as follows:

1. The user enters the employee ID and information about the course taken. This constitutes the incoming transaction record.
2. The MATCH command causes the RDBMS to search the table for an existing row with the specified employee ID.
3. If the employee row exists, the MODIFY continues to the next MATCH command.
4. If no row in the EMPINFO table exists with the specified employee ID, MODIFY rejects this transaction and routes control to the top of the FOCEXEC.
5. MATCH CNAME causes the RDBMS to search the COURSE table for an existing row with the specified course for the employee ID located in Step 2. If no such row exists, the MODIFY adds a row in the COURSE table. If the course row already exists, the MODIFY rejects the transaction as a duplicate.

The second example adds a row to the EMPINFO table for the new employee and adds a course for that employee to the COURSE table. If the employee ID already exists, the procedure adds only the course information to the COURSE table:

    MODIFY FILE ECOURSE
    CRTFORM LINE 1 
1.    "ID: <EMP_ID " 
2.  MATCH EMP_ID 
3.    ON NOMATCH CRTFORM LINE 2
      "       LAST: <LAST_NAME        FIRST: <FIRST_NAME </1 "
      "  HIRE DATE: <HIRE_DATE         DEPT: <DEPARTMENT "
      "        JOB: <CURR_JOBCODE    SALARY: <CURRENT_SALARY  </1"
      " BONUS PLAN: <BONUS_PLAN      ED HRS: <ED_HRS  </1"
      "COURSE NAME: <CNAME   "
      "YEAR: <YR_TAKEN  QTR: <QTR "
      "      GRADE: <GRADE "
      ON NOMATCH INCLUDE  
4.    ON MATCH CRTFORM LINE 10
      "COURSE NAME: <CNAME         YEAR: <YR_TAKEN  QTR: <QTR "
      "      GRADE: <GRADE " 
5.  MATCH CNAME
      ON NOMATCH INCLUDE
      ON MATCH REJECT
    DATA
    END

The MODIFY procedure processes as follows:

1. The user enters EMP_ID.
2. The MATCH command causes the RDBMS to search the EMPINFO table for an existing row for the specified employee ID.
3. If the employee row does not exist, the user enters the data for both the employee and the specified course. The procedure adds a row to each table.
4. If the employee already exists, the user enters only the course data.
5. The MATCH CNAME command causes the RDBMS to search the COURSE table for the specified course. If this course does not exist for this employee, the procedure adds it. If it does exist, the procedure rejects the transaction.

   Notice that the MATCH command only identifies CNAME. FOCUS automatically equates the value of EMP_ID with WHO, part of the key to COURSE.

x

FOCUS provides automatic referential integrity for deleting rows in a related set of tables. Just as with INCLUDE referential integrity, only tables described in a multi-table Master and Access File invoke FOCUS DELETE referential integrity.

Note: An attempt to use FOCUS DELETE referential integrity in conjunction with tables that have an RDBMS ON DELETE RESTRICT constraint on the child segments produces an error condition. When FOCUS attempts to delete a parent segment, the RDBMS restriction takes precedence and prevents the deletion.

When you delete a parent row (primary key) in a MODIFY or Maintain procedure, FOCUS automatically deletes all related rows (foreign keys) at the same time.

Top of page

Example: Using FOCUS DELETE Referential Integrity

When you delete an employee from the EMPINFO table in the ECOURSE Master File, FOCUS also deletes all rows from the COURSE table that represent courses the employee has taken:

    MODIFY FILE ECOURSE
    CRTFORM LINE 2
    "DELETE EMPLOYEE AND ALL COURSES </1" 
1.  "EMPLOYEE ID: <EMP_ID   " 
2.  MATCH EMP_ID
      ON MATCH COMPUTE DOIT/A1 = 'N'; 
      ON MATCH CRTFORM LINE 6  
3.    "EMPLOYEE TO BE DELETED: <D.EMP_ID </1"
      "         LAST NAME:   <D.LAST_NAME </1" 
      "         FIRST NAME:  <D.FIRST_NAME </1"
      "         HIRE DATE:   <D.HIRE_DATE </1"
      "         DEPARTMENT:  <D.DEPARTMENT </1"
      "         JOB CODE:    <D.CURR_JOBCODE </2 "
      "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 the RDBMS to search the EMPINFO table for an existing row with the specified employee ID.
3. If the row exists, the MODIFY displays information for verification purposes.
4. Once verified, FOCUS deletes the employee and all associated rows in both the EMPINFO and the COURSE tables. When FOCUS deletes a parent, it automatically deletes all associated related instances.

x

You may not always want to enforce FOCUS referential integrity. Consider a relationship in which COURSE is the parent table that contains the primary key and EMPINFO is the related table that contains the foreign key. If you delete a course offering, you do not want to delete all employees who have taken the course.

To handle this problem, specify the parameter WRITE=NO in the Access File for the related (foreign key) table. This gives you the ability to modify the COURSE table without affecting the data in the EMPINFO table. You can still use the data in the EMPINFO table for browsing or lookup tasks. This technique bypasses FOCUS referential integrity.

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