Do your Foreign Keys Hate You?

We all love our databases. We trust everything they do! How could we not? It's our baby.... well, probably not. How do we know when it's sneaking around behind your back asking questions it shouldn't and refusing to follow the plans? It's good to occasionally check up on certain aspects of your database. The Ozar family has a wonderful script called sp_BLITZ that does a fantastic job of identifying if you have some of the more common database issues and gives good general information.

All this is, is an easy way to have a script build a script for you to clear up what problems you may have. you're mileage may very, all sales final, returns not permitted, penguins accepted as payment..

If you're really curious what all this entails, there's quite a few sites with good information about these evil foreign keys we just cannot trust.

This is just a basic overview. When a key is trusted, it doesn't check the other table to ensure all the data matches, it just trusts it. When it's not trusted, you're doubling that work.

You could have had to disable that relationship at some point to remove a bad record, add in new data, or just simply because you were curious what happens when you disable it.

below is a script that will isolate what foreign keys are not trusted. It also provides a basic script that will make it trusted if everything matches up. It will error if your constraint is unable to be trusted.

exec sp_MSforeachdb @command1 ='
use ?
declare @DBName VarChar(300)
set @DBName = (select ''use '' + (Select DB_Name()))
select s.name as SchemaName, o.name as TableName, i.name as IndexName, o.object_id as Item,
@DBName + ''; '' + ''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] WITH CHECK CHECK CONSTRAINT ['' + i.name + '']'' as Repair_Script
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
'