DRI (Declarative Referential Integrity) does a lot of busy work for you we used to have to do ourselves when it wasn’t available. At one time, referential integrity had to be manually coded through the use of triggers. You can still do this today if you have a need.
In the trigger for a child table you would test for an existing parent when adding new rows, or changing a foreign key value. In a parent table you had to test for existing children in all the related tables whenever the key was modified or records were deleted. If you wanted cascading deletes, where you delete all child records for records being deleted from the parent table, you had to code that into a delete trigger.
Another nice aspect of DRI is that when you define one or more columns as a primary key it automatically creates a unique index on the selected columns. By default the primary key index is clustered; however that isn’t a requirement.
Creating a foreign key does not automatically create an index on the column in the child table pointing to the parent. If you locate children by the relationship from a parent table, then it is a good practice to add an index to the foreign key column or be sure it is included in a composite index if that makes more sense.
Just think about all the work you would need to do to code all the indexes and constraints now handled by DRI. By comparison, we have it really good. Now all we have to do is use it. Do you need clean data? Do you want to assure data integrity? DRI is a great place to start. So you have any good war stories on systems missing a good DRI design? Share it here, or drop an email to btaylor@sswug.org.
Cheers,
Ben