UNDERCOVER TOOLBOX: Find Untrusted Foreign Keys and Orphaned Key data

lost-1605501_1280

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:

2017-10-23 23_49_10

 

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:

2017-10-23 23_55_01
Failed to Re-enable WITH CHECK

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:

2017-10-24 00_00_01

 

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’

2017-10-24 00_05_35

 

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 🙂

One thought on “UNDERCOVER TOOLBOX: Find Untrusted Foreign Keys and Orphaned Key data

Add yours

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: