Sql Server: What is the benefit of using "Enforce foreign key constraint" when it's set to "NO"?

Question

I know the purpose of "Enforce foreign key constraint" in RDBMS. But is there any benefit when it's set to "NO" ?

Strobila

When you have two same primary key in two tables and you have to make a foreign key relation, then you have to set "Enforce foreign key constraint" to "No". This is pretty confusing, so I am giving the youtube url below, you will be clear after the end of this video. youtube.com/watch?v=H7uGGg1BQ2Y – Crampon

Answer 1

In normal production, this setting should never be set to NO.

But: when you're developing, or restructuring a database, or when you do e.g. a large bulk load of data that you'll need to "sanitize" (clean up), then it can make sense to turn off foreign key constraints to allow "non-valid" data to be loaded into a table. Of course, as I said - you shouldn't keep that setting turned off for a long period of time - you should then proceed to clean up the data, either delete those rows that are in violation of the FK constraint, or update their values so they match a parent row.

So again: in "normal" production mode, this setting should never be NO - but for specific tasks, it might help get the job done more easily. Use it with caution, and always turn the FK constraints back on as soon as you can!

Hl

Thank you for your note, but there are circumstances which we input data to a column in order to say for example all the predefined data. I mean if valid data are 1,2 and 3 then in order to say all of them we put -1 in the column. In this situations we should set "Enforce foreign key constraint" to NO in diagram. right? so is it really necessary to create a relationship? – Strobila

@Strobila : No. In your case it would be better to remove the foreign key altogether since it is serving no useful purpose at all. Instead you could create a CHECK constraint to validate the data values. – Halford

+1 for telling it like it is. I see this setting abused all the time in some companies that I contract for. It consistently leads to problems. I would add that I sometimes use it as an intermediate step when a foreign-key that should exist does not and I want to add it. Sometimes I'm not confident that all referencing applications are doing their operations in the correct order, and this lets me define the relationship immediately, test the applications in question (or search their code), and then turn enforcement on. – Religiose

So far as I am concerned, this is THE answer. I'd also add that when you're not certain a dependent application does its inserts in the correct order, it can be useful to use this setting when first adding some structure to a database to make sure nothing explodes. This is particularly true if the codebase does not have good test coverage. – Religiose

I just read this which seems to contradict the consensus here Why I Never Enforce Foreign Key Constraints – Hammad

@Amicable: I strongly disagree with that guy's ideas and thoughts - foreign keys ARE useful and help you maintain data integrity. If you just toss them overboard - good luck cleaning up your utter data MESS in a few years.... and I would DEFINITELY NOT call this a consensus ....$ – Hl

The article did sound counter intuitive, good to have that confirmed. Also I was referring to your answer as the consensus, poor phrasing on my part I meant to say "I just read this blog post...". – Hammad

I just stumbled over a situation where it should (can) be set to No. I have a User table and a Log table. I have connected the User and the Log with a Log.UserID field. But I also want to be able to delete Users so I set the foreign key to not enforce. This "requires" business logic to not require a valid User for a Log which happens to be my case. – Fervidor

@LosManos: my solution would be to not actually delete the users - instead, set a flag IsActive to false (so called "soft delete") – Hl

@Hl Flagging a record as not used is the technique, or as I prefer to call it today workaround due to how RDBs work, I have used for 15 years. In the User case one might have to think of resetting the UserName field too. But then this field that business wise is unique suddenly becomes nullable, or one sets a guid as UserName. If we exchange User for something more volatile the number of records might stack up and we'll have to erase records and implement true deleting functionality anyway. No answer is always correct. This has become a Discussion instead of Q&A,sorry for that. – Fervidor

Answer 2

Not in everyday usage, as far as I know. The times I've de-enforced foreign keys for a while are when there are problems with data and fixing them is hidered by relationship checks.

During bulk operations constraint checks are temporarily ignored in order to increase performance.

Rodomontade

Answer 3

It can also be useful in a data warehouse staging environment where you don't want to enforce the constraints before you have processed / cleansed the data, but you still want to be able to use the FK relationships to understand how the tables link to each other. These relationships can be picked up and displayed in third party tools (e.g. Visio, ERWIN, etc), so it can be useful metadata even though not strictly enforced.

Krasnoyarsk