Fixing Untrusted Foreign Key or Check Constraint

Untrusted constraints can be found when you alter/drop foreign key relationships and then add them back without the proper syntax.If you are deploying data through several tables, you might want to disable foreign keys on those tables during the deployment to ensure that all the required relationships have a chance to insert their data before validation.

Once you complete the update, you should run a check statement to ensure the Foreign Key is trusted. The difference in the check syntax is actually ridiculous… This check would not ensure the actual existing rows are validated to ensure compliance with the Foreign Key constraint.

alter table [dbo].[ChickenLiver] with check constraint [FK_EggDropSoup]

This check would check the rows contained in the table for adherence to the foreign key relationship and only succeed if the FK was successfully validated. This flags metadata for the database engine to know the key is trusted.

alter table [dbo].[ChickenLiver] with CHECK CHECK constraint [FK_EggDropSoup]

I originally worked through this after running sp_Blitz and working through the helpful documentation explaining Foreign Keys or Check Constraints Not Trusted.

Untrusted Check Constraints and FKs can actually impact the performance of the query, leading to a less optimal query plan. The query engine won’t know necessarily that the uniqueness of a constraint, or a foreign key is guaranteed at this point.

I forked the script from Brent’s link above and modified to iterate through and generate the script for running the check against everything in the database. This could be modified to be server wide if you wish as well. Original DMV query credit to Brent, and the the tweaks for running them against the database automatically are my small contribution.

Note: I wrote on this a while back, totally missed that I had covered this. For an older perspective on this: Stranger Danger… The need for trust with constraints

Get all Untrusted FK and Check Constraints in Database to Check and update as trusted

GitHub


/*******************************************************
    Check all constraints in db, FK, and check. 
    Run through each one and log if error occurs


    ------------------------- history ------------------------- 
    2015-08-06          sheldonhull         @ 13:27:21; adapted from the original basic info provided by [brentozar spblitz info](http://www.brentozar.com/blitz/foreign-key-trusted/) Thanks Brent!
*******************************************************/
set nocount on;
set xact_abort on;
declare @PrintOnly bit = 1;


/*******************************************************
    run check on each constraint to evaluate if errors
*******************************************************/
if object_id('tempdb..##CheckMe') is not null
	drop table ##CheckMe;

select
	temp_k =	identity(int, 1, 1)
	,X.*
into ##CheckMe
from
	(select

			type_of_check =											'FK'
			,'[' + s.name + '].[' + o.name + '].[' + i.name + ']'	as keyname
			,CheckMe =												'alter table ' + quotename(s.name) + '.' + quotename(o.name) + ' with check check constraint ' + quotename(i.name)
			,IsError =												convert(bit, null)
			,ErrorMessage =											convert(varchar(max), null)
		from
			sys.foreign_keys i
			inner join sys.objects o
				on i.parent_object_id = o.object_id
			inner join sys.schemas s
				on o.schema_id = s.schema_id
		where
			i.is_not_trusted = 1
			and i.is_not_for_replication = 0
		union all
		select
			type_of_check =											'CHECK'
			,'[' + s.name + '].[' + o.name + '].[' + i.name + ']'	as keyname
			,CheckMe =												'alter table ' + quotename(s.name) + '.' + quotename(o.name) + ' with check check constraint ' + quotename(i.name)
			,IsError =												convert(bit, null)
			,ErrorMessage =											convert(varchar(max), null)
		from
			sys.check_constraints i
			inner join sys.objects o
				on i.parent_object_id = o.object_id
			inner join sys.schemas s
				on o.schema_id = s.schema_id
		where
			i.is_not_trusted = 1
			and i.is_not_for_replication = 0
			and i.is_disabled = 0) as X





/*******************************************************
loop through all databases and execute query
*******************************************************/

declare	@TempK		int
		,@CheckMe	nvarchar(max)

declare curDatabase cursor fast_forward read_only local for select
			temp_k
			,CheckMe
		from
			##CheckMe

open curDatabase

fetch next from curDatabase into @TempK, @CheckMe

while @@fetch_status = 0
begin try

	if @PrintOnly =0
		exec sys.sp_executesql @CheckMe

	if @PrintOnly = 1
		print 'WHATIF: ' + @CheckMe


	update ##CheckMe
		set IsError = 0
	where
		temp_k = @TempK
	fetch next from curDatabase into @TempK, @CheckMe
end try
begin catch

	declare @ErrorMessage nvarchar(max) = isnull(error_procedure()+': ', '') + 'Error Line #: ' + convert(varchar(10), error_line()) + '; Error Message: ' + error_message()
	update ##CheckMe
		set	ErrorMessage	= @ErrorMessage
			,IsError		= 1
	where
		temp_k = @TempK

end catch

close curDatabase
deallocate curDatabase


select	* from ##CheckMe as i
-- where IsError = 1;