Referential integrity ensures that relationships between database tables remain consistent. Good database architecture uses SQL Server’s built in constraints to enforce referential integrity. For example a purchase order detail record should not be allowed to be added to the database unless the purchase order already exists. This is enforcement of the classic parent-child relationship.
SQL Server allows you to define cascading referential integrity constraints where one action causes a cascading effect of several other automatic actions. For example, deletion of a purchase order should automatically trigger deletion of its corresponding child detail records. If this is not done the detail table will continue to grow indefinitely with unusable orphaned records.
Quite often programmers forget to hook these up, or worse, simply ignore them. Since all of these features are optional it is up to the database architect to design them correctly.