Untrusted Foreign Keys
So what’s the deal with untrusted foreign keys?
When you have a foreign key in SQL Server there is an option to check that key when data is being changes, and it will prevent you from inserting or deleting rows that violate the foreign key constraint.
Often times when someone runs into an error on an import, or perhaps when they are adding a foreign key they turn off the option to do the check. What that means it that you can end up with bad data in your tables where the foreign key is in place.
There are also some performance issues associated with an untrusted foreign key and table elimination in queries.
Generally to fix this issue you need to do 2 things.
- First confirm that there are no violations on the foreign key column.
- Add the CHECK constraint to the foreign key.
After making those changes you will want to verify that your applications behave correctly when working with the tables you change.