Pros and cons of programmatically enforcing foreign key than in database

Question

It is causing so much trouble in terms of development just by letting database enforcing foreign key. Especially during unit test I can’t drop table due to foreign key constrains, I need to create table in such an order that foreign key constrain warning won’t get triggered. In reality I don’t see too much point of letting database enforcing the foreign key constrains. If the application has been properly designed there should not be any manual database manipulation other than select queries. I just want to make sure that I am not digging myself into a hole by not having foreign key constrains in database and leaving it solely to the application’s responsibility. Am I missing anything?

P.S. my real unit tests (not those that use mocking) will drop existing tables if the structure of underlying domain object has been modified.

Answer 1

In my experience, if you don't enforce foreign keys in a database, then eventually (assuming the database is relatively large and heavily used) you will end up with orphaned records. This can happen in many ways, but it always seems to happen.

If you index properly, there should not be any performance advantages to foreign keys.

So the question is, does the potential damage/hassle/support cost/financial cost of having orphaned records in your database outweigh the development and testing hassle?

In my experience, for business applications I always use foreign keys. It should just be a one-time setup cost to get your build scripts working correctly, and the data stability will more than pay for that over the life of an application.