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

The point of enforcing the rules in the database is that it's declarative - e.g. you do not have to write ton of code to handle it.

As far as your unit tests, just delete tables in the proper order. You just have to write a function to do it right once.

Answer 2

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.

Answer 3

Here's a nice discussion on that in a previous question on SO: What's wrong with foreign keys?. [Edit]: The argument is to make non-enforced foreign keys to get some of the pros if any of the cons apply.

Answer 4

It might seem like you can rely on your applications to follow implied rules, but unless you enforce them eventually someone will make a mistake.
Or maybe 5 years from now someone will do a tidy-up of old records "which are no longer needed" and not realise that there is data in other tables still referencing them. Then a few days/weeks later you or your successor gets the fun job of trying to repair the mess that the database has got in to. :-)

Answer 5

Your issues in development should not drive the DB design. Constantly rebuilding a DB is a developer use case, not a customer use case.

Also, the DB constraints help beyond your application. You never know what your customer might try to do. Don't over do it, but you need a few.

Answer 6

If the application has been properly designed there should not be any manual database manipulation other than select queries

What? What kind of koolaid are you drinking? Most databases applications exist to manipulate the data in the database not just to see it. Generally the whole purpose of the application is to add the new orders or create the new customer records or document the customer service calls etc.

Foreign keys are for data integrity. Data integrity is critical to being able to use the data with any reliability. Databases without data integrity are useless and can cause companies to lose money. This trumps your self-centered view that FKs aren't needed because they make development more complicated for you. The data is far more important than your convenience in running tests (which can be written to account for the FKs).