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:

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:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, TableName SYSNAME, UntrustedObject NVARCHAR(1000));
EXEC sp_MSforeachdb '
INSERT INTO #tmp
SELECT ''?'', QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id, DB_ID(''?''))) + ''.'' + QUOTENAME(OBJECT_NAME(parent_object_id, DB_ID(''?''))), + QUOTENAME(name)
FROM [?].sys.foreign_keys
WHERE is_not_trusted = 1 AND is_not_for_replication = 0 AND is_disabled = 0'
 
SELECT DBName, TableName, UntrustedObject, CommandToRemediate = N'USE ' + QUOTENAME(DBName) + N'; ALTER TABLE ' + TableName + N' WITH CHECK CHECK CONSTRAINT ' + UntrustedObject
FROM #tmp
DROP TABLE #tmp

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.

You can also find this script in my GitHub Gist here:

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

Remarks:

  • Please note that, due to how sp_MSforeachdb works, the script might fail if you have inaccessible databases. This could be fixed by adding an IF statement inside the dynamic command, which checks whether the database is online. Something like: IF EXISTS (SELECT NULL FROM sys.databases WHERE state_desc = ‘ONLINE’ AND name = ‘?’)
  • 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.

You may also find the following interesting:

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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