Maintaining Referential Integrity in a Database

It can be tricky sometimes to maintain referential integrity in a database that has a number of related tables.  Here’s an approach I’ve found that seems to work well:

1. Use foreign key constraints in the database to maintain referential integrity between two related tables.
2. Assign a default value to each table to avoid getting foreign key constraint errors if the value is not specified.   (This makes it easier to create new records without specifying all the field values to satisfy the foreign key constraints when the record is created).
3. When specifying foreign key constraints, there are two methods that are particularly critical to specify correctly:
   • How the database propagates updates
   • How the database propagates deletions
4. For updates, set the foreign key constraints to automatically Cascade updates in all cases
5. For deletions, there are two ways to handle them depending on the nature of the relationship between the two tables:
   • There is a "parent-child" relationship between the tables (For example, the "Orders" table is the parent of the "Order Line Items" table). In this situation, set the foreign key constraint between the two tables to Cascade the deletions. For example, deleting an order should also delete all the order line items associated with that order and not leave them behind as "orphans".
   • There is a "type" or a modifier relationship between the tables rather than an ownership relationship (For example, the "Records" table has a "Record Storage Location" column that is joined to the Record Storage Locations table to specify where the record is stored). In this situation, set the foreign key constraint between the two tables to "Set Default" on deletion. For example, if the a given record storage location is deleted, you wouldn't necessarily delete all the records that were stored in that location. They should have been moved to another location prior to this if, in fact they are to continue to exist, but if there are stragglers in the database, you would probably want to know about it. By setting the default value for this field which would typically be an unassigned value, you can easily spot these "stragglers" and determine if they need to be reassigned.

I hope that is helpful to others...it seems self-evident, but I assure you it wasn't until I took some time to think through it. Chuck