It's leading to a lot trouble when it comes to development simply by letting database enforcing foreign key. Especially throughout unit test I can’t drop table because of foreign key constrains, I have to create table such a purchase that foreign key constrain warning won’t get triggered. The truth is I don’t see an excessive amount of reason for letting database enforcing the foreign key constrains. When the application continues to be correctly designed there shouldn't be any manual database manipulation apart from choose queries. Among the finest to make certain that i'm not digging myself right into a hole by not getting foreign key constrains in database and departing it exclusively towards the application’s responsibility. Shall We Be Held missing anything?

P.S. my real unit tests (not individuals which use mocking) will drop existing tables when the structure of underlying domain object continues to be modified.

In my opinion, if you do not enforce foreign secrets inside a database, then eventually (presuming the database is comparatively large and heavily used) you'll finish track of orphaned records. This could happen in lots of ways, however it always appears to occur.

Should you index correctly, there shouldn't be any performance benefits of foreign secrets.

So now you ask ,, does the possibility damage/hassle/support cost/financial price of getting orphaned records inside your database over-shadow the expansion and testing hassle?

In my opinion, for business programs I usually use foreign secrets. It will you need to be a 1-time setup cost to obtain your build scripts working properly, and also the data stability will a lot more than pay for your within the existence of the application.

Your issues in development shouldn't drive the DB design. Constantly repairing a DB is really a developer use situation, not really a customer use situation.

Also, the DB constraints help away from application. Who knows what your customer might attempt to do. Don't exaggerate it, but you'll need a couple of.

The purpose of enforcing the guidelines within the database is the fact that it's declarative - e.g. you don't have to create lot of code to deal with it.

So far as your unit tests, just remove tables within the proper order. You just need to write a function to get it done right once.

It could appear as if you can depend in your programs to follow along with implied rules, but unless of course you enforce them eventually someone will get it wrong.
Or possibly five years from now someone is going to do a tidy-from old records "that are no more needed" and never understand that there's data in other tables still referencing them. A couple of days/days later you and your successor will get the enjoyment job of attempting to correct the mess the database has directly into. :-)

Here is a nice discussion on that inside a previous question on SO: What's wrong with foreign keys?. [Edit]: The argument would be to make non-enforced foreign secrets to obtain a few of the pros if the cons apply.

When the application continues to be correctly designed there shouldn't be any manual database manipulation other than choose queries

What? What type of koolaid are you currently consuming? Most databases programs exist to control the information within the database not just in view it. Usually the whole reason for the applying would be to add the brand new orders or produce the new customer records or document the client service calls etc.

Foreign secrets are for data integrity. Data integrity is crucial to having the ability to make use of the data with any reliability. Databases without data integrity are useless and may cause companies to get rid of money. This trumps oneself-centered view that FKs aren't needed simply because they make development more difficult for you personally. The information is much more important than your convenience in running tests (which may be written to take into account the FKs).