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

	/*******************************************************
	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;

view raw FixUntrustedFKAndCheckConstraints.sql hosted with by GitHub