David, you're right in that I don't usually require referential integrity in my databases. I don't feel it's always necessary to use it unless you want to make sure the database always observes cascaded updates and deletes.

For those of you who don't know what this means, referential integrity is basically making sure that data in related tables matches up properly. For example, if you have customers and orders, you don't want to have a situation where there is an order that doesn't have a customer.

However, even in this example, I can think of a situation where I might (walk-in, pays cash). So I wouldn't always want to FORCE referential integrity for this relationship. You might WANT to enforce it when you have orders and line items. You wouldn't want a line item without an order that it belongs to.

So you see, referential integrity is something that I use on a case-by-case basis. It depends on what I'm doing, and the situation at hand.

One of the benefits of referential integrity is that you can enforce cascading updates and deletes. A cascade update would occur if you changed the customer ID, for example. Any related orders for that customer would be updated. However, since I almost always use an autonumber to perform this relationship, it would never change.

Cascade deletes ARE very handy. This would happen if you delete a customer and want all of his related records to be deleted. However this could also get you in trouble because if you delete Joe Smith and all of his orders disappear, your bottom line just changed for your accounting. So as you can see, even this needs to be taken into consideration. Again, a good example would be if you deleted an Order record, all of the line items would be deleted automagically too.

One of the major hindrances of referential integrity is that if you somehow mess up a table and need to restore it from a backup, you can't just delete THAT table if it's in a relationship. You first have to break all of the global relationships and then delete the table. Import the new table and then re-create all of the relationships.

This is why I generally find that it's much easier NOT to use referential integrity unless I absolutely need it. It's mostly a matter of personal choice, but it has served me well for many, MANY years (yes, I'm old and have been doing this for a while).

The only major downside to not using referential integrity is that you do end up with some garbage in your tables - usually in that situation where you delete an order and the details are left hanging in your table. You can make sure to manually delete these in your VB code when the parent record is deleted, or just run a Find Unmatched query once a year or so.

I hope this answers your question.