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:
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)
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.
Pingback: T-SQL Tuesday 143 – Short Powershell code to move DB files in AlwaysOn – Eitan Blumin's Blog