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. You can find this script in my GitHub Gist here:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, FullTableName AS QUOTENAME(SchemaName) + N'.' + QUOTENAME(TableName), UntrustedObject SYSNAME);
INSERT INTO #tmp(DBName, SchemaName, TableName, UntrustedObject)
EXEC sp_MSforeachdb 'IF EXISTS (SELECT * FROM sys.databases WHERE state_desc = ''ONLINE'' AND name = ''?'' AND DATABASEPROPERTYEX(''?'', ''Updateability'') = ''READ_WRITE'')
BEGIN
USE [?];
SELECT ''?'', OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), [name]
FROM [?].sys.foreign_keys
WHERE is_not_trusted = 1 AND is_not_for_replication = 0 AND is_disabled = 0;
END'
SELECT
*
, CommandToRemediate = N'USE ' + QUOTENAME(DBName) + N'; ALTER TABLE ' + FullTableName + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(UntrustedObject) + N';'
FROM #tmp
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:

/************** Find Orphaned Records **************
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
More info: https://eitanblumin.com/2018/11/06/find-and-fix-untrusted-foreign-keys-in-all-databases/
****************************************************/
DECLARE
@ForeignKeyName SYSNAME = 'FK_MyTable_MyOtherTable'
, @PrintOnly BIT = 0
DECLARE
@FKId INT,
@ChildTableID INT,
@ParentTableID INT,
@CMD NVARCHAR(MAX),
@ColumnNullabilityCheck NVARCHAR(MAX) = N''
SELECT
@FKId = object_id,
@ChildTableID = parent_object_id,
@ParentTableID = referenced_object_id
FROM sys.foreign_keys
WHERE name = @ForeignKeyName
IF @FKId IS NULL
BEGIN
RAISERROR(N'Foreign Key %s was not found in current database!', 16, 1, @ForeignKeyName);
GOTO Quit;
END
SELECT
@CMD = ISNULL(@CMD + CHAR(13) + CHAR(10) + N' AND ', N'') + N'ctable.' + QUOTENAME(cc.name) + N' = ptable.' + QUOTENAME(pc.name)
, @ColumnNullabilityCheck = @ColumnNullabilityCheck + CHAR(13) + CHAR(10) + N' AND ctable.' + QUOTENAME(cc.name) + N' IS NOT NULL'
ChildTable = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id))
, ChildColumn = QUOTENAME(cc.name)
, ParentTable = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id))
, ParentColumn = QUOTENAME(pc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS cc
ON fkc.parent_object_id = cc.object_id
AND fkc.parent_column_id = cc.column_id
INNER JOIN sys.columns AS pc
ON fkc.referenced_object_id = pc.object_id
AND fkc.referenced_column_id = pc.column_id
WHERE fkc.constraint_object_id = @FKId
SET @CMD = N'SELECT ctable.*
FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(@ChildTableID)) + '.' + QUOTENAME(OBJECT_NAME(@ChildTableID)) + N' AS ctable
WHERE NOT EXISTS
(SELECT NULL FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(@ParentTableID)) + '.' + QUOTENAME(OBJECT_NAME(@ParentTableID)) + N' AS ptable
WHERE ' + @CMD + N')'
+ ISNULL(@ColumnNullabilityCheck, N'')
PRINT @CMD
IF @PrintOnly = 0
EXEC (@CMD);
Quit:
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

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

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