Skip to content
Sir, please step aside and check your constraints
Home » Find and fix untrusted Foreign Keys in all databases

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:

 ALTER TABLE [MyTable] WITH CHECK CHECK CONSTRAINT [FK_Name]; 

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:

https://gist.github.com/EitanBlumin/fa4bdeb0cbf84674f1841d40369e930b

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.

Remarks

  • 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:

https://gist.github.com/EitanBlumin/f81d272a70668cc20bb3741396f14b54

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

1 thought on “Find and fix untrusted Foreign Keys in all databases”

  1. Pingback: T-SQL Tuesday 143 – Short Powershell code to move DB files in AlwaysOn – Eitan Blumin's Blog

Leave a Reply

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