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. |
In this section: |
Performance considerations usually make it preferable to have RDBMS indexes on both primary and foreign keys.
The following definitions help explain referential integrity:
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.
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.
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.
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 data 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 data 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.
The data adapter provides some level of automatic referential integrity for tables described in a multi-table Master File. |
In this section: FOCUS INCLUDE Referential Integrity FOCUS DELETE Referential Integrity Inhibiting FOCUS Referential Integrity Example: |
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 MODIFY facility syntax provides automatic referential integrity for inserting new rows in a related set of tables. The following rules apply:
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.
The following examples demonstrate referential integrity when adding new rows. The scenarios are:
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 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 9
"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:
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 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.
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:
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.
| Information Builders |