SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Detect and Repair Non Trusted Foreign Keys

By Ronald Hensbergen,

As a DBA, you might get in the situation where someone has disabled a foreign key constraint to insert or delete data, but with enabling the constraint again, the WITH CHECK option wasn't used. Brent Ozar described this situation already a few years ago: https://www.brentozar.com/blitz/foreign-key-trusted/. As he mentioned, the problem is that for these untrusted constraints aren't used in query plans. This usually leads to a major performance impact.

I too had this situation, but then the problem is: how to detect easily of the referential integrity is still in place or not? And if so, how to enable the constraint? Especially if you're working with many tables, it's not easy to resolve this issue. Happily, all the information is stored in system tables, so I wrote a query to create the code to see if the referential integrity is still good, and also code to enable the constraint again with the WITH CHECK. This can then be encapsulated in a loop like a cursor or something.

I created this code for SQL Server 2016. The concatenation I took from the chosen answer in https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv.

Beware: for large tables, enabling the constraint can take quite a while, so execute this part during a time window where locks with a long duration won't mess up other activities (too much).

I hope you like it!

Total article views: 242 | Views in the last 30 days: 21
Related Articles

Disabling & enabling all of MS Access constraints

How do I disable and enable constraints for bulk load


Check if Constraint is enabled or disabled

This is a script to find all constraints and check if they are enabled or disabled.



Violating the primary key constraint


Quickly Enabling and Disabling Constraints and Triggers

In some cases, you may have to be able to quickly disable all the constraints and triggers in a data...


Dataware Performance Degradation - Urgent Situation.

Dataware Performance Degradation - Urgent Situation.

foreign keys    
with check