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.