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: 203 | Views in the last 30 days: 16
 
Related Articles
FORUM

Disabling & enabling all of MS Access constraints

How do I disable and enable constraints for bulk load

SCRIPT

Check if Constraint is enabled or disabled

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

FORUM

Constraints

Violating the primary key constraint

ARTICLE

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...

FORUM

Dataware Performance Degradation - Urgent Situation.

Dataware Performance Degradation - Urgent Situation.

Tags
constraint    
foreign keys    
nocheck    
with check    
 
Contribute