1. What is referential integrity in DB2/400?
Method to ensure that a value in a “dependent field” in one file exists in the independent field of another file. For example: A method to ensure that the customer number in the order file exists in the customer file. This is often done in programs but it can also be set as database rules using the command
Add Physical File Constraint (ADDPFCST)
What is field reference file in DB2/400?
The field reference file is used as a data dictonary of sorts. It contains field/column definitions (field names, sizes and types, titles and column headings.
So our Field Reference file might contain the definition for customer Number
FDRCUST# 6S 0 TITLE(‘Customer Number’)
Then our OrderHeader file could contain the field definition
ORDCUST# R REFFLD(FDRCUST#/FDRFILE)
And in our Customer File
CUCUST# R REFFLD(FDRCUST#/FDRFILE)
We know the two fields will have the same structure because they inhereted it from the same parent.
<i>This is often done in programs but it can also be set as database rules using the command …</i>
Technically, if it is done in program logic, it isn’t “referential integrity” — it’s just program logic. The actual question is <i>What is referential integrity in DB2/400?</i>
By using referential integrity supplied by DB2 (by the database itself), program logic doesn’t need to be created. The integrity is enforced even when programming isn’t involved.
DFU, for example, cannot be used to circumvent the integrity. ODBC, interactive SQL, even other business programming cannot change content of files if the change violates the rules of referential integrity that have been applied to the database.
Note that a “field reference file” is essentially unrelated to “referential integrity”. A field reference file is simply a way to supply predictable and standard field definitions when creating new files or adding fields to existing files.