Designing the Data Structure › Choose to Distribute or Replicate Data › Challenges of Distributed and Replicated Data › Referential Integrity

Referential Integrity

Referential integrity requires that data that has been affected by a procedure must satisfy all integrity rules for that procedure to execute successfully. For example, when an ORDER is created, at least one ORDER ITEM must also be created.

Referential integrity considerations for database and application design:

• Location of boundaries—Every reference from data stored in one location to data stored in another must satisfy integrity rules before a change to data can be accepted as successful.

  Any physical boundary between related data can cause extra data accesses and checking to maintain referential integrity. The boundary should be positioned to minimize access by procedures to related data stored in more than one location.

• Relationships across a boundary—Mandatory relationships across a physical boundary must be maintained by procedure logic. This logic should identify and attempt to fix any discrepancies.

  For example, assume that CUSTOMERS may be stored in one location and EMPLOYEES in another location, and that each CUSTOMER must be the responsibility of an EMPLOYEE. The name of the RESPONSIBLE EMPLOYEE is defined as an attribute of CUSTOMER. When a CUSTOMER is created, the procedure checks the existence of a responsible EMPLOYEE. When an EMPLOYEE is deleted, a procedure checks that there is no CUSTOMER who is the responsibility of the EMPLOYEE.

• Use of foreign keys—Foreign keys can be useful to represent relationship memberships across a physical boundary between two data stores. Where the target DBMS is unable to maintain integrity across different physical databases, then the designer must specify procedure logic to maintain the data values in step.
• Deletions—Deletions, such as cascade deletes, pendant deletes, and restricted deletes, that affect related data held in more than one location, can be very difficult to complete successfully.

Ideally, the DBMS should make all checks. If this is not possible, data integrity should be maintained by procedures.