SQL Server tracks untrusted Foreign keys in sys.Foreign keys with a column called is_not_trusted, there may be a number of reasons why a Foreign key may have become untrusted below are a couple of examples:
- Foreign key was disabled using the ‘NOCHECK’ option then re-enabled using ‘CHECK’ (not to be confused with ‘WITH CHECK’)
- Foreign key was disabled using the ‘NOCHECK’ option , Primary key data was Deleted and the Foreign key was Enabled only using ‘CHECK’ (Again not to be confused with ‘WITH CHECK’)
So what happens when you try and enable a Foreign key ‘WITH CHECK’ (Check existing data for referential integrity), if the data is consistent then this is going to succeed however if Rows have been deleted and the Primary key data no longer exists but the Foreign key data does for example then this is going to fail miserably.
You can expect to see an error like this one:
Msg 547, Level 16, State 0, Line 8
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_AnotherTable”. The conflict occurred in database “SQLUNDERCOVERTRAINING”, table “dbo.test”.
So what do these statements look like?
--Disabled Foreign Key ALTER TABLE [dbo].[AnotherTable] NOCHECK CONSTRAINT [FK_AnotherTable] --Re-enable Foreign Key (No Check of existing data) ALTER TABLE [dbo].[AnotherTable] CHECK CONSTRAINT [FK_AnotherTable] --Re-enable Foreign Key and Check of existing data ALTER TABLE [dbo].[AnotherTable] WITH CHECK CHECK CONSTRAINT [FK_AnotherTable]
For More information about Foreign Key relationships and some potential performance impacts of untrusted foreign keys be sure to check out This Post over at BrentOzar.com , and this Video By Kendra Little also @ BrentOzar.com.
So if you are interested in not only finding these foreign keys but also attempting to re-enable them then please read on, we have a couple of scripts here that will help you not only find these untrusted Foreign keys (the easy part) but they will produce statements to re-enable them ‘WITH CHECK’ and if they fail, produce a Select statement for you to use that will find the offending rows that are missing from the Primary key table, cool huh? Well I’d like to think so but then I am completely bias 
In addition to providing a script to re-enable the Keys, the script also produces some details such as the Foreign key name, Table name ,Foreign key columns, Primary key table and Primary key referenced column/s.
We also added a little Parameter in there so that you can switch between Re-enable ‘WITH CHECK’ mode, or simple run in Check data mode (Produce select statements only to check the data) so if you really wanted to you could script out all the T-SQL statements that will check for any missing Primary keys referenced by the Foreign keys for untrusted Foreign keys.
Let me show you an example:
I will run this with @EnableForeignKey set to 1 to produce the Re-enable scripts.
As you can see there are currently 2 Untrusted Foreign Keys:
Here are the contents of the SQL_Script Column for the first row (Foreign key FK_AnotherTable)
BEGIN TRY
RAISERROR('Enabling Foreign key [dbo].[AnotherTable].[FK_AnotherTable] WITH CHECK...',0,0) WITH NOWAIT
ALTER TABLE [dbo].[AnotherTable] WITH CHECK CHECK CONSTRAINT [FK_AnotherTable]
END TRY
BEGIN CATCH
RAISERROR('FAILED: Orphaned FK Data exists for FK - [dbo].[AnotherTable].[FK_AnotherTable] , see output for a script to identify the data',0,0) WITH NOWAIT
SELECT '[dbo].[AnotherTable].[FK_AnotherTable]' AS Failed_ForeignKey,'SELECT FK.[id3] ,FK.[id4]
FROM [dbo].[test] PK
RIGHT JOIN [dbo].[AnotherTable] FK ON PK.[id]= FK.[id3] AND PK.[id2]= FK.[id4]
WHERE PK.[id] IS NULL AND PK.[id2] IS NULL AND FK.[id3] IS NOT NULL AND FK.[id4] IS NOT NULL' AS Identify_Orphaned_ForeignKeys_Script
END CATCH
Now lets execute the code:
It failed because there is Orphaned data here, but the good thing is we know which Foreign key failed and we have a handy bit of code in column ‘Identify_Orphaned_ForeignKeys_Script’ so lets copy and paste this code and run it to see if we can identify the offending row/s
Here is the script:
SELECT FK.[id3] ,FK.[id4] FROM [dbo].[test] PK RIGHT JOIN [dbo].[AnotherTable] FK ON PK.[id]= FK.[id3] AND PK.[id2]= FK.[id4] WHERE PK.[id] IS NULL AND PK.[id2] IS NULL AND FK.[id3] IS NOT NULL AND FK.[id4] IS NOT NULL
And the Results are:
So it looks like the offending row here is the column [id3] and [id4] both with a value of 3, so for this example I will go and delete this data from the Foreign key table to show the process succeeding after we fix my orphaned row (you may need to handle your data differently this is just for demonstration).
DELETE FROM [dbo].[AnotherTable] WHERE [id3] = 3 AND [id4] = 3
(1 row affected)
So now if I run the command again to re enable ‘WITH CHECK’
It Succeeds!
Before I show you the code, these queries are intended to be a means of finding data that exists in the Foreign key table but not the Primary key table, you may find that for very large tables these queries are not very efficient so please be careful when you run them.
There are two versions, the first one is a Cursor version and the second is a CROSS APPLY version feel free to use which ever you prefer , I know some people are not fond of cursors so that’s the main reason for making the Cross apply version
Cursor Version:
/**********************************************
--Author: Adrian Buckman
--Create Date: 22/10/2017
--Description: Show Untrusted Foreign key information including Foreign key name, FK table, FK Columns, PK Table , PK Columns reference
--Produce SQL Statements to Re enable Untrusted Foreign Keys using @EnableForeignKey = 1 and if these fail to re enable then statements to check the data will be produced.
(c) SQL Undercover 2017
www.sqlundercover.com
**********************************************/DECLARE @EnableForeignKey BIT = 1-- 1: Produce Enable Foreign key scripts, 0: Produce a script to identify if any Orphaned foreign keys exist
DECLARE @SortBy TINYINT = 3 --Order by Column number specify a value from 1-5
DECLARE @ColumnName NVARCHAR(128)
DECLARE @FKObjectID INT
DECLARE @PKFKCRels NVARCHAR(1000)
SET NOCOUNT ON;
SET @ColumnName = CHOOSE(@SortBy,'Orphaned_ForeignKeys_Script','ForeignKey','PK_Tablename','PK_Columns','FK_Columns')
IF @ColumnName IS NOT NULL
BEGIN
IF OBJECT_ID('TempDB..#OutputList') IS NOT NULL
DROP TABLE #OutputList;
CREATE TABLE #OutputList
(
ID INT IDENTITY(1,1),
Orphaned_ForeignKeys_Script NVARCHAR(4000),
ForeignKey NVARCHAR(1000),
PK_Tablename NVARCHAR(256),
PK_Columns NVARCHAR(1000),
FK_Columns NVARCHAR(1000)
);
--Cursor through all non trusted Foreign keys
DECLARE FK_Cur CURSOR STATIC FORWARD_ONLY LOCAL
FOR
SELECT
[Object_id]
FROM [sys].[foreign_keys] [FKeys]
WHERE [FKeys].[is_not_trusted] = 1
OPEN FK_Cur
FETCH NEXT FROM FK_Cur INTO @FKObjectID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @PKFKCRelCols NVARCHAR(1000) = ''
--For each non trusted Foreign key Match each Foreign key column with it's Referenced PK counterpart
DECLARE Column_Cur CURSOR FORWARD_ONLY LOCAL
FOR
SELECT
(
SELECT
'PK.'+ QUOTENAME(COL_NAME([FKCols].[referenced_object_id],[FKCols].[referenced_column_id])) +
'= FK.'+ QUOTENAME(COL_NAME([FKCols].[parent_object_id],[FKCols].[parent_column_id])) + ' AND '
FROM [sys].[foreign_key_columns] AS [FKCols]
WHERE [FKCols].[constraint_object_id] = [FKeys].[object_id]
AND [FKCols].[referenced_column_id] = [ReferenceCols].[column_id] FOR XML PATH('')
) AS [PK_FK_Columns_By_Position]
FROM [sys].[foreign_keys] [FKeys]
LEFT JOIN [sys].[foreign_key_columns] [FKCols] ON [FKeys].[object_id] = [FKCols].[constraint_object_id]
LEFT JOIN [sys].[all_columns] [ReferenceCols] ON [FKCols].[referenced_object_id] = [ReferenceCols].[object_id]
AND [FKCols].[referenced_column_id] = [ReferenceCols].[column_id]
WHERE [FKeys].[object_id] = @FKObjectID
OPEN Column_Cur
FETCH NEXT FROM Column_Cur INTO @PKFKCRels
WHILE @@FETCH_STATUS = 0
BEGIN
--Build a list of columns including Aliases and 'AND' clauses to be used in the joins in the scripted output
SET @PKFKCRelCols = @PKFKCRelCols + @PKFKCRels
FETCH NEXT FROM Column_Cur INTO @PKFKCRels
END
CLOSE Column_Cur
DEALLOCATE Column_Cur
--Strip additional AND added from the cursor above
SET @PKFKCRelCols = LEFT(@PKFKCRelCols,LEN(@PKFKCRelCols)-4)
--Build the Orphaned Foreign keys script output and include additional columns that show the Foreign key name, the PK table name, PK columns and FK columns
INSERT INTO #OutputList (Orphaned_ForeignKeys_Script, ForeignKey, PK_Tablename, PK_Columns,FK_Columns)
SELECT DISTINCT
CASE
WHEN @EnableForeignKey = 0
THEN
'SELECT FK.'+REPLACE(FK_Columns,',',' ,FK.')+'
FROM '+PK_Tablename+' PK
RIGHT JOIN '+QUOTENAME(PARSENAME(ForeignKey,3))+'.'+QUOTENAME(PARSENAME(ForeignKey,2))+' FK ON '+@PKFKCRelCols+'
WHERE PK.'+REPLACE(PK_Columns,',',' IS NULL AND PK.')+' IS NULL AND FK.'+REPLACE(FK_Columns,',',' IS NOT NULL AND FK.')+ ' IS NOT NULL
'
ELSE
'BEGIN TRY
RAISERROR(''Enabling Foreign key '+[ForeignKey]+' WITH CHECK...'',0,0) WITH NOWAIT
ALTER TABLE '+QUOTENAME(PARSENAME(ForeignKey,3))+'.'+QUOTENAME(PARSENAME(ForeignKey,2))+' WITH CHECK CHECK CONSTRAINT '+QUOTENAME(PARSENAME(ForeignKey,1)) +'
END TRY
BEGIN CATCH
RAISERROR(''FAILED: Orphaned FK Data exists for FK - '+[ForeignKey]+ ' , see output for a script to identify the data'',0,0) WITH NOWAIT'+
'
SELECT '''+[ForeignKey]+''' AS Failed_ForeignKey,''SELECT FK.'+REPLACE(FK_Columns,',',' ,FK.')+'
FROM '+PK_Tablename+' PK
RIGHT JOIN '+QUOTENAME(PARSENAME(ForeignKey,3))+'.'+QUOTENAME(PARSENAME(ForeignKey,2))+' FK ON '+@PKFKCRelCols+'
WHERE PK.'+REPLACE(PK_Columns,',',' IS NULL AND PK.')+' IS NULL AND FK.'+REPLACE(FK_Columns,',',' IS NOT NULL AND FK.')+ ' IS NOT NULL'' AS Identify_Orphaned_ForeignKeys_Script
END CATCH
'
END AS Orphaned_ForeignKeys_Script,
[ForeignKey],
[PK_Tablename],
[PK_Columns],
[FK_Columns]
FROM
(
SELECT
QUOTENAME([PKSchema].[name])+'.'+QUOTENAME((OBJECT_NAME([FKeys].[referenced_object_id]))) AS [PK_Tablename],
STUFF(
(
SELECT ','+QUOTENAME(COL_NAME([PKCols].[referenced_object_id],[PKCols].[referenced_column_id]))
FROM [sys].[foreign_key_columns] [PKCols]
WHERE [PKCols].[constraint_object_id] = [FKeys].[object_id] FOR XML PATH('')
),1,1,'') AS [PK_Columns],
QUOTENAME(SCHEMA_NAME([FKeys].[Schema_id]))+'.'+QUOTENAME(OBJECT_NAME([FKeys].[Parent_object_id]))+'.'+QUOTENAME([FKeys].[name]) AS [ForeignKey],
STUFF(
(
SELECT ','+QUOTENAME(COL_NAME([FKCols].[parent_object_id],[FKCols].[parent_column_id]))
FROM [sys].[foreign_key_columns] AS [FKCols]
WHERE [FKCols].[constraint_object_id] = [FKeys].object_id FOR XML PATH('')
),1,1,'') AS [FK_Columns]
FROM [sys].[foreign_keys] [FKeys]
LEFT JOIN [sys].[foreign_key_columns] [FKCols] ON [FKeys].[object_id] = [FKCols].[constraint_object_id]
LEFT JOIN [sys].[objects] [PKObject] ON [PKObject].[object_id] = [FKeys].[referenced_object_id]
LEFT JOIN [sys].[schemas] [PKSchema] ON [PKObject].[schema_id] = [PKSchema].[schema_id]
WHERE [FKeys].[object_id] = @FKObjectID
) DERIVED;
FETCH NEXT FROM FK_Cur INTO @FKObjectID
END
CLOSE FK_Cur
DEALLOCATE FK_Cur
EXEC (
N'SELECT
Orphaned_ForeignKeys_Script,
ForeignKey,
PK_Tablename,
PK_Columns,
FK_Columns
FROM #OutputList
ORDER BY '+@ColumnName+' ASC')
END
ELSE
BEGIN
RAISERROR('Invalid @Sortby Value set , only enter values ranging from 1 - 5 inclusive',11,0)
END
Cross Apply Version:
/**********************************************
--Author: Adrian Buckman
--Create Date: 22/10/2017
--Description: Show Untrusted Foreign key information including Foreign key name, FK table, FK Columns, PK Table , PK Columns reference
--Produce SQL Statements to Re enable Untrusted Foreign Keys using @EnableForeignKey = 1 and if these fail to re enable then statements to check the data will be produced.
**********************************************/DECLARE @EnableForeignKey BIT = 1 --1: Produce Enable Foreign key scripts, 0: Produce a script to identify if any Orphaned foreign keys exist
IF OBJECT_ID('TempDB..#UntrustedFKs') IS NOT NULL
DROP TABLE #UntrustedFKs;
--Populate the Temp Table with Untrusted Foreign Key information
SELECT DISTINCT [FKeys].[object_id] ,
QUOTENAME([PKSchema].[name])+'.'+QUOTENAME((OBJECT_NAME([FKeys].[referenced_object_id]))) AS [PK_Tablename],
STUFF(
(
SELECT ','+QUOTENAME(COL_NAME([PKCols].[referenced_object_id],[PKCols].[referenced_column_id]))
FROM [sys].[foreign_key_columns] [PKCols]
WHERE [PKCols].[constraint_object_id] = [FKeys].[object_id] FOR XML PATH('')
),1,1,'') AS [PK_Columns],
QUOTENAME(SCHEMA_NAME([FKeys].[Schema_id]))+'.'+QUOTENAME(OBJECT_NAME([FKeys].[Parent_object_id]))+'.'+QUOTENAME([FKeys].[name]) AS [ForeignKey],
STUFF(
(
SELECT ','+QUOTENAME(COL_NAME([FKCols].[parent_object_id],[FKCols].[parent_column_id]))
FROM [sys].[foreign_key_columns] AS [FKCols]
WHERE [FKCols].[constraint_object_id] = [FKeys].object_id FOR XML PATH('')
),1,1,'') AS [FK_Columns]
INTO #UntrustedFKs
FROM [sys].[foreign_keys] [FKeys]
LEFT JOIN [sys].[foreign_key_columns] [FKCols] ON [FKeys].[object_id] = [FKCols].[constraint_object_id]
LEFT JOIN [sys].[objects] [PKObject] ON [PKObject].[object_id] = [FKeys].[referenced_object_id]
LEFT JOIN [sys].[schemas] [PKSchema] ON [PKObject].[schema_id] = [PKSchema].[schema_id]
WHERE [FKeys].[is_not_trusted] = 1
--Build Orphaned Foreign Key Scripts and show Table and Key Relationships
SELECT DISTINCT
CASE
WHEN @EnableForeignKey = 0
THEN
'SELECT FK.'+REPLACE(FK_Columns,',',' ,FK.')+'
FROM '+PK_Tablename+' PK
RIGHT JOIN '+QUOTENAME(PARSENAME(ForeignKey,3))+'.'+QUOTENAME(PARSENAME(ForeignKey,2))+' FK ON '+[PK_FK_Columns_By_Position]+'
WHERE PK.'+REPLACE(PK_Columns,',',' IS NULL AND PK.')+' IS NULL AND FK.'+REPLACE(FK_Columns,',',' IS NOT NULL AND FK.')+ ' IS NOT NULL
'
ELSE
'BEGIN TRY
RAISERROR(''Enabling Foreign key '+[ForeignKey]+' WITH CHECK...'',0,0) WITH NOWAIT
ALTER TABLE '+QUOTENAME(PARSENAME(ForeignKey,3))+'.'+QUOTENAME(PARSENAME(ForeignKey,2))+' WITH CHECK CHECK CONSTRAINT '+QUOTENAME(PARSENAME(ForeignKey,1)) +'
END TRY
BEGIN CATCH
RAISERROR(''FAILED: Orphaned FK Data exists for FK - '+[ForeignKey]+ ' , see output for a script to identify the data'',0,0) WITH NOWAIT'+
'
SELECT '''+[ForeignKey]+''' AS Failed_ForeignKey,''SELECT FK.'+REPLACE(FK_Columns,',',' ,FK.')+'
FROM '+PK_Tablename+' PK
RIGHT JOIN '+QUOTENAME(PARSENAME(ForeignKey,3))+'.'+QUOTENAME(PARSENAME(ForeignKey,2))+' FK ON '+[PK_FK_Columns_By_Position]+'
WHERE PK.'+REPLACE(PK_Columns,',',' IS NULL AND PK.')+' IS NULL AND FK.'+REPLACE(FK_Columns,',',' IS NOT NULL AND FK.')+ ' IS NOT NULL'' AS Identify_Orphaned_ForeignKeys_Script
END CATCH
'
END AS Orphaned_ForeignKeys_Script,
[ForeignKey],
[PK_Tablename],
[PK_Columns],
[FK_Columns]
FROM #UntrustedFKs FKeys
CROSS APPLY (SELECT STUFF(CAST((
SELECT
(
SELECT DISTINCT
' AND PK.'+ QUOTENAME(COL_NAME([FKCols].[referenced_object_id],[FKCols].[referenced_column_id])) +
'= FK.'+ QUOTENAME(COL_NAME([FKCols].[parent_object_id],[FKCols].[parent_column_id]))
FROM [sys].[foreign_key_columns] AS [FKCols]
WHERE [FKCols].[constraint_object_id] = [FKeys2].[object_id]
AND [FKCols].[referenced_column_id] = [ReferenceCols].[column_id] FOR XML PATH('')
)
FROM [sys].[foreign_keys] [FKeys2]
LEFT JOIN [sys].[foreign_key_columns] [FKCols] ON [FKeys2].[object_id] = [FKCols].[constraint_object_id]
LEFT JOIN [sys].[all_columns] [ReferenceCols] ON [FKCols].[referenced_object_id] = [ReferenceCols].[object_id]
AND [FKCols].[referenced_column_id] = [ReferenceCols].[column_id]
WHERE [Fkeys].[Object_Id] = [FKeys2].[object_id]
FOR XML PATH(''),TYPE)
AS NVARCHAR(1000)),1,5,'') AS [PK_FK_Columns_By_Position]) AS PKFKCRelCols
Thanks for Reading
