So while we're using foreign keys in our current project, I've heard the argument before that enabling foreign key checking within a development environment simply puts roadblocks in front of developers - code should not rely on foreign keys being in place.
I was wondering what people thought about this idea - when developing, do you keep foreign keys enabled in your development environment, or do you turn them off?
Absolutely keep them enabled. The existence of foreign key constraints is actually very useful in development; not using the constraints can allow for lazy development; moreover, it can allow for some serious problems to creep into the code, that could cause some difficulties when moving to a production environment.
I ALWAYS keep them enabled. You want to make sure that your code isn't going to do something that violates FK rules. If you have them removed there is nothing preventing the code from putting in bad data. Additionally we will use tools such as CodeSmith to help with some of our automation pieces, and with CodeSmith, it can automatically generate query procedures for us if we have the FK's in place.
Overall, I am a firm believer that the development environment should be a very close replica to that of the production environment. If you don't have them it is very possible to have code that will fail in production because it violates a FK constraint, and it will work just fine in test.
I highly recommend that you keep them on. It provides a saftey net to make sure that your Data Layer is functioning properly. That is unless you are working with ETL applications and you need to turn off contraints on purpose for data loads.
Foreign keys should not rely on code being in place. I'd consider this stupid advice. It's easy to screw up the referential integrity of a database, and developers make plenty of mistakes. In the same way as a c# developer appreciates type safety, anyone developing for databases will appreciate the safeguarding of referential integrity.
However, in the case of bulk inserts etc, it might be quicker to switch off FK constraints, but only after thorough testing with them enabled.
Hi, I always keep FK's enabled in my environment. The reason for this is if I code around not having FK's something could break later with them turned on.
Even though it may cause a little bit more headache to have them turned on, it is worth it in the long run in my opinion.
You keep them enabled. If they're enabled in your test and production environments they should be in your development environment as well or you're simply pushing potential problems to another environment, which will actually make things take longer to fix.
I ~always have FK enabled, I understand the premise of the argument that application logic should enforce the data contraints and not rely on the DB contraints themselves, this aside, but i dont think i'd be the first person who has missed a cascading constraint before in my application logic, and the database has picked this up for me, I imagine this is very common, and having this picked up in a dev cycle, is much more pallettable than in a producution env!
In my opinion, having Foreign keys in a development environment will not be a roadblock, but will be a benefit. Data integrity is a good thing and foreign keys are a great way to enforce it.
That's like saying "running tests that fail" presents difficulties for the development team. The whole point of constraints is insuring the validity and correctness of your system.
Take the time to develop the required support infrastructure for your development process to facilitate adding and modifying the database.
In addition to the many reasons in the other posts, disabling foreign keys in development has a big risk that you write code that won't run in production. For example, say you create an orderline before you create an order. That will run fine without foreign keys, but fail when they are enabled.
I'm heartened to see the other answers are nearly unanimous that you should keep constraints enabled in your development.
I would add that your code needs to handle exceptions when an attempted data INSERT/UPDATE/DELETE causes a constraint violation. You need your code to work correctly when (not if) this occurs in production. So you must enable constraints during development and testing.
I'm going to say, let's remove the constraints.
Down with FKs!
and remove all the indices, all other constraints as well.
Just kidding. :)
Of course.. keep it as close to production as possible - scale down the size of the data and please please please SCRUB the data before you distribute it.