Implementation of Referential Integrity

Lyon Technologies, Inc. uses the power of the database to implement referential integrity throughout the system.

Referential integrity is a database management safeguard that ensures that the references between tables in the database are valid; i.e., that every foreign key has a corresponding primary key.

Let's use the example from Transaction-Based Programming of a help desk application. For example, we have several different problem status codes—New, In Progress, Waiting on Customer, Waiting on Test Results, and Closed. The problem status code is a primary key in a lookup table called Problem_Status_Code. The Problem_Status_Code data field shows up as a foreign key in the Customer_Problem table, providing the status of the Customer_Problem. If a record is deleted from the Problem_Status_Code table, the corresponding records in the Customer_Problem table will be left without a matching reference. They are considered to be "orphan" records—there is no match for the Problem_Status_Code field in the Problem_Status_Code table.

Referential integrity can be enforced at the application level or at the database level. If it is enforced at the application level, then the application must capture every instance in which a primary or foreign key could be inserted, updated, or deleted by the user. Although many applications attempt to enforce referential integrity at the application level, this is nearly impossible to do; therefore, it is better to implement it at the database level.

Within a database system such as Oracle®, referential constraints can be defined. These constraints enforce the references between the database tables whenever an INSERT, UPDATE, or DELETE occurs. For example, if an administrator were to delete a value in a lookup table that was currently in use by an existing Customer_Problem, this could cause an integrity problem in the database. To prevent this, Lyon Technologies would apply database constraints to enforce referential integrity in such a way that the delete action would not be allowed unless the corresponding Customer_Problem record were changed so that it no longer uses the corresponding data lookup value.

print this explanation | close this window