Providing Referential Integrity

   

Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

For example, assume that your application has both a Titles table and a Publishers table as shown in the following table.

Titles table Publishers table
ti_isbn (key) pu_id (key)
ti_title pu_name
ti_yearpublished pu_address
pu_id (foreign key) pu_phone

Referential integrity requires that these two tables must be synchronized. That is, each publisher identification (pu_id) in the Titles table must also exist in the Publishers table.

Your application cannot just delete the pu_id row from the Publishers table because that would leave the pu_id in the Titles table without a reference. It would be permissible, however, to delete the pu_id row from the Publishers table and also delete every row in the Titles table that has the same pu_id. Such an action would maintain referential integrity for these two tables.

In a similar manner, your application can't just add a row to the Titles table without a valid pu_id already in the Publishers table. To do so would require "bad" data in the pu_id field. So, your application must ensure a valid Publishers row before inserting the related Titles row.

The actual implementation of referential integrity depends entirely on the data storage engine you choose and your application's design requirements. Historically, applications using mainframe VSAM files used application code to handle referential integrity. Today, even if your application uses SQL Server, that doesn't mean you must use triggers, foreign keys, constraints, and cascading deletes to maintain referential integrity. You might again choose to handle referential issues with application-based code.