Identifying and Fixing Untrusted Foreign Keys

Untrusted foreign keys can lose their integrity and affect the performance of queries, especially those running on tables.

To identify and fix untrusted foreign keys:

1. Retrieve untrusted foreign key information from your database by running the following query against all users on your database:

   SELECT    o.name as TableName, 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

   If your database contains untrusted foreign keys, they are displayed on the Results tab in Microsoft SQL Server Management Studio (SSMS).

2. If you found untrusted foreign keys in step 1, run the following query for each key:

   ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT keyname

   Replace tablename and keyname with the name of the table and key displayed on the Results tab.