What is a Foreign Key?

A foreign key is a link between 2 tables that is used to enforce referential integrity in the database.

For example, if you have an order and a customer table, there is probably a logical relationship between the 2 tables. An order can’t exist without being linked to a customer record.

When trusted, these keys help ensure that the data in your database stays ‘clean’ and logical.

By establishing trusted foreign keys in SQL Server between your tables, the optimizer is able to make some assumptions about the data and therefore make more efficient execution plans for your queries.

Foreign keys are trusted by default when created since checks are done to ensure the data all lines up. If the data does not match as expected, errors are relayed and the key is not created. Once established they keys will prevent ‘bad’ data from being entered into your database so long as they remain trusted.

 

What is an Untrusted Foreign Key?

An untrusted foreign key is one that has had the referential integrity of the relationship removed. SQL Server is unable to ‘trust’ that the data is clean in both tables and therefore isn’t exactly sure of the best way to proceed. This can start to show itself by queries getting slower as the optimizer starts to perform extra checks to make sure the data it is getting is good.

 

  How Do They Become Untrusted?

We have established that foreign keys are important in relational databases since they check & help enforce the referential integrity of the data. Sometimes though, when doing large bulk loads and similar operations, SQL Server can slow down due to the large number of checks being done.

One of the ways to improve the performance of these bulk loads is to disable the foreign keys on the tables being loaded. This is usually a safer & faster process than just dropping and recreating the keys.

Foreign keys then become untrusted when they are not re-enabled correctly after the bulk load or similar operation is completed.

Usually the foreign key is re-enabled with the CHECK CONSTRAINT option in the ALTER TABLE statement. This is fine to re-enable the foreign key, but it does not tell SQL Server to re-verify the integrity of the data.

Because of this, SQL will not know if the relationship can still be trusted, that the data is ‘clean’ between the 2 tables. This results in the SQL Server optimizer ignoring the foreign key restraint and checking the data integrity itself with extra processes added to your query execution plan.

 

  How Do I Trust a Foreign Key Again?

Before you are able to trust foreign keys again, you have to identify the ones that are no longer trusted first.

This first snippet of code goes through your database and identify the foreign keys & constraints that are enabled yet not trusted. (We don’t have to worry about disabled keys)

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
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
        AND i.is_disabled = 0

This will identify the schema, object and name of the keys that are not trusted in your database.

Once you have that result set you can use the following statement to re-establish the trust for each result in the list:

ALTER TABLE <s.name from results>.<o.name from results> WITH CHECK CHECK CONSTRAINT <i.name from results>

The CHECK CHECK in the syntax seems odd, but it is just due to the alignment of 2 different options with the alter table statement.

The first is the WITH CHECK statement. This tell the ALTER statement to validate the table contents against the foreign key. By default when re-enabling an existing foreign key or constraint this is set to WITH NOCHECK. You have to explicitly define it when re-enabling a foreign key or constraint.

The second is the CHECK CONSTRAINT statement. This is used to configure if the constraint is enabled or disabled. CHECK CONSTRAINT is enabled, while NOCHECK CONSTRAINT disables the foreign key.

If you have a lot of results from the previous script, you can use the following script which will output the ALTER TABLE statements so you can copy & paste the ones you like.

SELECT  'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS keyname
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
        AND i.is_disabled = 0

This script could be easily modified to also apply the alter, but I personally prefer to look at the results and changes to be made prior to having it run, just in case.

The script can also be modified to enable all foreign keys & constraints by using ALL rather than the [i.name] but be sure that you want all keys and constraints enabled or disabled first.

Any way you do it, you want to make sure your foreign keys and constraints are trusted so the SQL Server optimizer can create efficient plans for your queries.