Verifying Foreign Keys in MS SQL Server

During a recent data check we noticed that foreign key columns contained values which were not stored in the referenced tables. The foreign key constraints were there, but they had been deactivated with the WITH NOCHECK clause.

This blog post was quite helpful to understand disabled and trusted foreign key constraints.

Using this information, I wrote the following script to verify foreign key constraints

DECLARE @t sysname, @fk sysname, @s NVARCHAR(1000)

DECLARE c CURSOR FOR
SELECT t.name, fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id
ORDER BY 1, 2

OPEN c 
FETCH c INTO @t, @fk

WHILE @@FETCH_STATUS = 0 BEGIN
	
	SET @s = 'ALTER TABLE ' + @t + 
		' WITH CHECK CHECK CONSTRAINT ' + @fk
	PRINT @s
	
	BEGIN TRY
	EXEC (@s)
	END TRY
	BEGIN CATCH
		PRINT 'Conflict in ' + @t + N'.' + @fk
		DBCC CHECKCONSTRAINTS (@t)
	END CATCH
	
	FETCH c INTO @t, @fk
END

CLOSE c
DEALLOCATE c

SELECT t.name, fk.name, fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id
WHERE fk.is_not_trusted = 1
ORDER BY 1, 2

The script tries to enable each foreign key constraint it finds in the database using the WITH CHECK CHECK CONSTRAINT clause.

If the check fails, it calls DBCC CHECKCONSTRAINTS which returns the set of recordings violating the foreign key constraint.

Finally, the list of all foreign keys is selected which are set to “not trusted” (see linked blog above).

The result of the script execution shows you which tables and records need to be cleaned up.

Run the script repeatedly after data cleanup until a single empty result set is returned.

About these ads

One Response to Verifying Foreign Keys in MS SQL Server

  1. David says:

    Nice, useful script. I did change the SET @s line to put brackets around the identifiers, because it wasn’t working on tables with keyword names like [User].

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

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: