Referential Integrity

Since primary and foreign key values establish relationships between separate tables, it is important to maintain these values in a consistent manner throughout the data source. The term referential integrity defines the type of consistency that should exist between foreign keys and primary keys

Performance considerations usually make it preferable to have RDBMS indexes on both primary and foreign keys.

The following definitions help explain referential integrity:

Primary key is the column or combination of columns whose value uniquely identifies a row within the table. None of the key columns can contain null values. A table can only have one designated primary key.
The employee ID (EMP_ID) is the primary key in the sample EMPINFO table. The values for the EMP_ID field make each row unique, since no two employees can have the same identification number.
Foreign key is a column or combination of columns in one table whose values are the same as the primary key of another table. The foreign key may be unique or non-unique, but its value must match a primary key value in the other table or be null.
The employee ID (or WHO field) in the sample COURSE table is a foreign key. This field is similar to the primary key in the EMPINFO table in that it contains the employee ID of every employee who has taken a course. It contains multiple rows for those employees who have taken more than one course.
Referential integrity describes the synchronization of these key field values:
- INCLUDE Referential Integrity. A value must exist as a primary key before it can be entered as a foreign key. For example, a specific employee ID must exist in the EMPINFO table before a course can be added for that employee in the COURSE table.
- DELETE Referential Integrity. If a primary key is deleted, all references to its value as a foreign key must be deleted, set to null, or changed to reflect an existing primary key value.

The RDBMS can define and enforce referential integrity rules (constraints).

FOCUS can also provide referential integrity for those tables described in a multi-table Master File and Access File pair. The following sections discuss both types of referential integrity constraints.

RDBMS Referential Integrity

The RDBMS provides the ability to define relationships between tables by embedding referential integrity constraints in the table definitions. The RDBMS prohibits data changes that violate the rules, and applications using the adapter respect these defined constraints.

Note: Tables you create with the FOCUS CREATE FILE command do not contain primary or foreign key definitions and, therefore, do not participate in RDBMS referential integrity unless you can add primary and foreign key definitions to such tables.

Violations of RDBMS referential integrity rules result in an error. The adapter posts the return code it receives from the RDBMS to the FOCERROR variable. Your MODIFY or Maintain procedure can test this value.

Referential integrity violations do not terminate MODIFY or Maintain procedures, so you need not use the FOCUS SET ERRORRUN ON command to continue MODIFY processing.

With RDBMS referential integrity in place, you do not need FOCUS referential integrity to invoke some level of automatic referential integrity support. You may wish to maintain your tables in separate Master Files and let the RDBMS take care of all referential integrity enforcement.

You may also choose to describe the tables as related (using multi-table Master and Access Files) and take advantage of FOCUS referential integrity.

If you use both FOCUS referential integrity and RDBMS referential integrity, the RDBMS referential integrity takes precedence in cases of conflict. Make sure you are familiar with the RDBMS referential integrity constraints on the tables involved as well as FOCUS referential integrity behavior. Check with your RDBMS database administrator for specific referential integrity constraints.

FOCUS Referential Integrity

The adapter provides some level of automatic referential integrity for tables described in a multi-table Master File.

The following sections describe the rules and techniques for ensuring or inhibiting FOCUS INCLUDE and DELETE referential integrity. The examples use the ECOURSE Master File, a multi-table description that relates the EMPINFO and COURSE tables. (See File Descriptions and Tables.)

FOCUS INCLUDE Referential Integrity

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.

Using FOCUS INCLUDE Referential Integrity

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

Add a course for an employee only if data for the employee ID already exists.
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:

The user enters the employee ID and information about the course taken. This constitutes the incoming transaction record.
The MATCH command causes the RDBMS to search the table for an existing row with the specified employee ID.
If the employee row exists, the MODIFY continues to the next MATCH command.
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.
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:

The user enters EMP_ID.
The MATCH command causes the RDBMS to search the EMPINFO table for an existing row for the specified employee ID.
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.
If the employee already exists, the user enters only the course data.
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.

FOCUS DELETE Referential Integrity

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.

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:

The user enters the employee ID.
The MATCH command causes the RDBMS to search the EMPINFO table for an existing row with the specified employee ID.
If the row exists, the MODIFY displays information for verification purposes.
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.

Inhibiting FOCUS Referential Integrity

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.

Did you find this helpful?

Yes No