Sir, please step aside and check your constraints

Find and fix untrusted Foreign Keys in all databases

Not Trusted Foreign Key Constraints are such for which integrity of the underlying data is not guaranteed. This most commonly happens when you perform Bulk Insert into a table that has a FK constraint, or when you disable a FK constraint.

Having not trusted foreign keys can be dangerous for data integrity, because it’s possible to have invalid data in your table (despite the existence of the foreign key).

In order to “repair” an untrusted foreign key, you run the following command:


Of course, if your database happens to have a lot of untrusted FKs, then it could be a drag to write such a command for each, and even more so if you have multiple databases with untrusted foreign keys.

For this reason I’ve written a script to generate commands to repair ALL not trusted foreign keys in ALL databases. You can find this script in my GitHub Gist here:

This script is also good for when you just want to check whether you have untrusted foreign keys in any of your databases or not.


  • Be very careful when running the remediation commands on a production database. Performing a Foreign Key check will cause a scan on the entire child table, joining with the parent table. If the tables are large, this can be a very heavy operation which will lock the tables until the operation is complete. Therefore you should do this during a safe maintenance window. (thanks to @GuyGlantser for the remark)

What’s Next?

Let’s say that you’ve run your remediation scripts, but you got an exception telling you that the foreign key cannot be validated because there are records that violate it! Oh no! What do you do??

Msg 547, Level 16, State 0, Line 10
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Name". The conflict occurred in database "MyDB", table "dbo.MyParentTable", column 'PKColumn'.

For this purpose exactly, I’ve written the following additional script, which receives a foreign key name as a parameter, and returns the exact records that violate the foreign key’s integrity. It’s in my GitHub Gists here:

The script automatically discovers the foreign key configuration and generates the proper SELECT command to find the orphaned data.

Simply copy and paste into the script the name of the foreign key from the “UntrustedObject” column, and run it. Again, be mindful of production databases with very large tables, which may result in very heavy queries.

See Also

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.